Oracle TimeStamp update/insert year interpret

Question

Am I right in thinking within Oracle, if I were to do a an Update/Insert to a TimeStamp column with the following...

UPDATE tblBlah set TimeStampColumn = to_timestamp('01-01-99 12:00 AM', 'MM/DD/YY HH:MI AM')

Oracle would interpret the year as an already occurred year, 1999 as opposed to a future year 2099 ?

Wouldn't it be easier to try than to ask here? `SELECT to_timestamp('01-01-99 12:00 AM', 'MM/DD/YY HH:MI AM') FROM DUAL` — GolezTrol, May 10 '13 at 15:01
[What is the difference between oracle's 'yy' and 'rr' date mask?](http://stackoverflow.com/questions/19058/what-is-the-difference-between-oracles-yy-and-rr-date-mask) — Tim Lehner, May 10 '13 at 15:02

score 4 · Accepted Answer · answered May 10 '13 at 15:01

YY will take the last two digits and overwrite those of the current year.

select to_timestamp('01-01-99 12:00 AM', 'MM/DD/YY HH:MI AM') from dual
-->
January, 01 2099 00:00:00+0000

Use RR instead:

select to_timestamp('01-01-99 12:00 AM', 'MM/DD/RR HH:MI AM') from dual
-->
January, 01 1999 00:00:00+0000

RR will:

Given a year with 2 digits, returns a year in the next century if the year is <50 and the last 2 digits of the current year are >=50; returns a year in the preceding century if the year is >=50 and the last 2 digits of the current year are <50.

Example at SQL Fiddle.

Oracle TimeStamp update/insert year interpret

1 Answers1