Search code examples
sqloracle-databasedateformaty2k

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

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?

Solution

  • 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