How does Access store a date?

[Guest]

How does Access store a date? Ex. 1 June 2004

 

[Jeff Conrad]

To quote the legendary MVP John Vinson:

"Access stores Date/Time fields as floating point numbers, counts of
days and fractions of a day (times) since midnight, December 30, 1899.
The format merely controls how the value is displayed, not what's stored."

and

"Because in Access a Date/Time value is NOT A STRING. It's stored
internally as a double floating point count of days from a start
point; it can be formatted in many ways but underneath it's still just
a number."

 

[Joseph Meehan]

I forget the exact format but as I recall it is stored as the year
followed by the day of the year decimal point followed by the time in
seconds. So your example would be something like 2004153.123 for 2minutes
3 seconds after midnight.

 

[John Vinson]

To quote the legendary MVP John Vinson:

Hey! I'm not a legend, I'm real! <pinching myself to see> Yep.


John W. Vinson[MVP]

 

[John Vinson]

I forget the exact format but as I recall it is stored as the year
followed by the day of the year decimal point followed by the time in
seconds. So your example would be something like 2004153.123 for 2minutes
3 seconds after midnight.

ummm... not actually. It's a running count of days since midnight,
December 30, 1899 in days and fractional days. #06/01/2004 00:02:03#
is, for example:

?cdbl(#06/01/2004 00:02:03#)
38139.0014236111

Or going the other way,

?cdate(38250.6666666666)
9/20/2004 4:00:00 PM


John W. Vinson[MVP]

 

[Jeff Conrad]

You're a legend in MY book John.

 

[Joseph Meehan]

ummm... not actually. It's a running count of days since midnight,
December 30, 1899 in days and fractional days. #06/01/2004 00:02:03#
is, for example:

?cdbl(#06/01/2004 00:02:03#)
38139.0014236111

Or going the other way,

?cdate(38250.6666666666)
9/20/2004 4:00:00 PM


John W. Vinson[MVP]

I knew it was something like that.