Wrong MS Access Date interpretation from Linked Oracle table

[violeta_barajas]

Hi,



I have a front end 2003 .mdb MS Access application with Oracle linked tables. When opening one of the oracle linked tables in MS Access, the date column is not being interpreted correctly since 01-Jan-00 (oracle date) is being interpreted as 01/01/1900. I've tried formatting the column in MS Access but this does not work. In Oracle the column data type is DATE.


Does anyone the reason for this issue?​



Regards,
Violeta

[CJ_London]

date/time fields are actually stored as a form of decimal numbers. the bit before the dp relates to the date and the bit after (for access) is the time expressed as number of seconds/no of seconds in a day. The bit before the db for today (11th Aug 2016) in Access is 42593, tomorrow it will be 42594. So zero is 01/01/1900 (i.e. there have been 42593 days since that date).

I've never had to think about it before, but Oracle probable has the same date zero, but is showing a 00 year rather than 1900. If you format your access date as dd-mmm-yy it will probably show the same thing as you see in Oracle

Either way the date you are getting is 0 - presumably meaning 'no date'

[RuralGuy]

This might help: https://docs.oracle.com/cd/B28359_01...type.htm#i1847

[violeta_barajas]

Thank you Ajax, you are right. I totally assumed that it was 2000 because 1900 does not make sense (in this case for this app). But it might be an error of entry of data. I queried the data converting the date to char and that's how it came the 1900.


Thanks!

[violeta_barajas]

Thanks RuralGuy!

[RuralGuy]

Glad to help where I can.