Access date format

General
1

I am trying to convert some dates that are stored in an old Access database that is accessed by a VB6 program that is being converted. I cannot figure out what the relationship is between the stored date and the number that comes out on the other end after doing an ODBC connection and bringing the data over. Once I get them into SQL date formats I will be able to do what I need with them, but how they are stored is Access I am not clear on. The field is listed as a “Date/Time” field in Access and is 8 Bytes long. Here are some examples of what they look like in the Access and what number comes out when it is read in through the ODBC connect:

3/11/1974 = 2215036801
11/18/1962 = 1858118401
8/02/1966 = 1975017601

I know they are legitimate dates because when you sort on them it puts them in the exact same order they are stored in Access. And all of them have 001, 201, 401, 601 or 801 as the 3 right most digits, I am guessing that has something to do with the time stamp that is not filled in?

In any case if anyone knows how these numbers relate to the dates stored in SQL I would be grateful if you could provide a clue.

Thanks!

2

Looks like seconds from the start of 1904 (which exactly matches the classic framework TotalSeconds property). The number for midnight is always a multiple of 86’400, which explains the even01 endings (one second past midnight).

3

I think that is it. I just did a couple of quick checks in Excel and the math adds up. I’ll try it in the code this afternoon. Thank you!