What is the difference TO_DATE('21-09-1989','DD-MM-YY') and TO_DATE('21-09-89','DD-MM-YY')?

user_12345906 0
Viewed 149

Below query returns the result:

SELECT * 
FROM EMPLOYEES 
WHERE HIRE_DATE = TO_DATE('21-09-1989','DD-MM-YY');

where as if I change the date from 21-09-1989 to 21-09-89 returns nothing.

SELECT * 
FROM EMPLOYEES 
WHERE HIRE_DATE = TO_DATE('21-09-89','DD-MM-YY');

What is the issue here?

y2k format date oracle sql
3 Answers

If you use the YY as the year then it returns the year which is in the current century i.e. 2000-2099. In your case -- 2089

If you use the YYYY then the exact year is returned. -- in your case 1989

If you use RR then the year which is between 1950-2049 is returned. -- in your case 1989

So 

TO_DATE('21-09-1989','DD-MM-YY') --> 21-09-1989
-- oracle is smart to detect the given format as YYYY

TO_DATE('21-09-89','DD-MM-YY') --> 21-09-2089
-- as 2089 is between 2000-2099

TO_DATE('21-09-89','DD-MM-RR') --> 21-09-1989
-- as 1989 is between 1950-2049
user_115656290 
TO_DATE('21-09-89','DD-MM-YY')

evaluates to Sep 21 2089

user_4462790

As already explained in other answer about the difference between 'YY' and 'YYYY' format and its implications. You could easily avoid problems by using the ANSI DATE literal:

DATE '1989-09-21'

The ANSI date literal contains no time portion, and must be specified in exactly this format ('YYYY-MM-DD').

Also, HIRE_DATE is a DATE data type which also has a time portion. Therefore, your WHERE condition might not return correct values:

WHERE HIRE_DATE = TO_DATE('21-09-1989','DD-MM-YY')

To remove the time portion you must use TRUNC to compare with only date portion:

WHERE TRUNC(HIRE_DATE) = DATE '1989-09-21'
user_39896080
Related