Wednesday, July 12, 2006

Oracle Timestamp

Interesting note on using timestamps in Oracle. Trying to set a timestamp field (my_timestamp) by using the following sql will NOT work correctly:

update my_table
set my_timestamp = '01-JUL-2006'

This will end up setting the timestamp equal to July 1, 2020 and something. So, using the following sql will work,

update my_table
set my_timestamp = TO_TIMESTAMP ('01-jul-06 14:10:10.123000', 'DD-Mon-RR HH24:MI:SS.FF')

Not sure if this is due to some nls* settings or something, but don't have time to look into it.