#1899-DEC-30 00:00:00# converts to to_date('00:00:00', 'HH24:MI:SS')
I'm converting a system from Access to Oracle DB.
Many functions in the system use time ... In Access the time can be referrenced by #00:00:00#. Internally, Access appears to store the time as 1899-DEC-30 00:00:00 (probably stored as number of (milli)seconds from 1899/12/30)
This data happily migrates to the Oracle DB ...
Problem is 'select to_date('00:00:00', 'HH24:MI:SS') from dual' in Oracle results in the first day of the current month of the current year ... for eg 2005-JUL-01 00:00:00 and the data is stored as 1899-DEC-30 00:00:00. So the SQL requests return 0 rows.
How can I work around this?
Note that a call in Access via ODBC to Oracle does the following -
#13:00:00# converts to
to_date('13:00:00', 'HH24:MI:SS')
#1899-DEC-30 13:00:00# converts to
to_date('13:00:00', 'HH24:MI:SS')
#1899-DEC-31 13:00:00# converts to
to_date('1899-12-31 13:00:00','YYYY-MM-DD HH24:MI:SS')