What is wrong with this sql?
This gives me 0 results:
Select * from DOC where CREATION_TIME > to_TIMESTAMP('01-Jan-69 11.59.16 AM','dd-Mon-YY HH12.mi.ss.FF3AM');
When I run this as:
Select * from DOC where CREATION_TIME > '01-JAN-69 11.59.16.000000000 AM';
I get results just fine. The table schema is setup with CREATION_TIME as a Timestamp type with the format seen in the second query.
This is because,
select TO_TIMESTAMP('01-Jan-69 11.59.16 AM','dd-Mon-YY HH12.mi.ss.FF3AM') from dual
returns 01.01.2069 11:59:16 (note 2069 instead of 1969 here)
To convert to the correct year use,
TO_TIMESTAMP('01-Jan-69 11.59.16 AM','dd-Mon-RR HH12.mi.ss.FF3AM')
From the documentation
The RR datetime format element is similar to the YY datetime format element, but it provides additional flexibility for storing date values in other centuries. The RR datetime format element lets you store 20th century dates in the 21st century by specifying only the last two digits of the year.
If you use the TO_DATE function with the YY datetime format element, then the year returned always has the same first 2 digits as the current year. If you use the RR datetime format element instead, then the century of the return value varies according to the specified two-digit year and the last two digits of the current year.
If the specified two-digit year is 00 to 49, then
If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.
If the specified two-digit year is 50 to 99, then
If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.
You probably feel very lucky assuming a string will be translated correctly as a timestamp.
The ANSI/ISO way to notate a timestamp literal is timestamp yyyy-mm-dd[.ff] hh24:mi:ss, e.g. -
select timestamp '1969-01-01 11:59:16' from dual;
select timestamp '1969-01-01 11:59:16.123' from dual;
to_timestamp()and not rely on implicit conversions (which are correct 50% of the time at best). Do not let this incident (the "Y2K problem") discourage you from the best practice, which is to useto_timestamp()as you were trying to. That's the right way to write queries.