How do i convert a date into a number?

[Guest]

I have a table which stores a date as yyyymmdd. I am quering from this
table, and wanting to append this date into another table, but the field I'm
appending to is a numeric field instead of a date field. I can not change
this to a date field because of how other data gets appended into the table.

When I run my query, it converts the date to some number and not actually
the date as a number. For example, I have a date of 20060630 but its
inserting 38898 insead of the numbers "20060630".

Is there a way to see the date as numbers and only insert those numbers into
the table?

Thanks in advanced for any and all help!

 

[Douglas J. Steele]

Access stores date/times as 8 byte floating point numbers, where the integer
portion represents the date as the number of days relative to 30 Dec, 1899,
and the decimal portion represents the time as a fraction of a day.

30 June, 2006 happens to be exactly 38898 days since 30 Dec, 1899. If you go
to the Immediate Window (Ctrl-G), type the following, and hit Enter, you'll
see this:

?Format(38898, "yyyymmdd")
20060630

Similarly, you can format a date as numeric, and get the same:

?Format(#6/30/2006#, "General Number")
38898

 

[John Vinson]

When I run my query, it converts the date to some number and not actually
the date as a number. For example, I have a date of 20060630 but its
inserting 38898 insead of the numbers "20060630".

Is there a way to see the date as numbers and only insert those numbers into
the table?

You can generate a long integer of the type you need using

Val(Format([datefield], "yyyymmdd"))

John W. Vinson[MVP]

 

[Guest]

Thank you very much for the explaination! I never understood what number it
was displaying before. Is there a reason why they picked 30 Dec 1899?

 

[Guest]

John,

That was perfect! Thank you very much for your help.

Laura

When I run my query, it converts the date to some number and not actually
the date as a number. For example, I have a date of 20060630 but its
inserting 38898 insead of the numbers "20060630".

Is there a way to see the date as numbers and only insert those numbers into
the table?

You can generate a long integer of the type you need using

Val(Format([datefield], "yyyymmdd"))

John W. Vinson[MVP]

 

[Douglas J. Steele]

Would you believe it was because Lotus 1-2-3 had a bug in it?

The story I've heard is that Lotus 1-2-3 had January 1st, 1900 as 1, January
2nd, 1900 as 2 and so on. However, the developers of Lotus 1-2-3 mistakenly
assumed that 1900 was a leap year. When Microsoft added VBA to Excel,
apparently they found it easier to use December 30th, 1899 as 0, rather than
December 31st to make it easier to deal with importing data from Lotus.

 

[Brendan Reynolds]

Apparently, this is why ...

http://www.joelonsoftware.com/items/2006/06/16.html