4

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?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Sudan IS
  • 43
  • 1
  • 6

3 Answers3

8

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
Popeye
  • 35,427
  • 4
  • 10
  • 31
3
TO_DATE('21-09-89','DD-MM-YY')

evaluates to Sep 21 2089

vc 74
  • 37,131
  • 7
  • 73
  • 89
0

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'
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    The "issue" here is _exactly_ what the Y2k bug was all about . . . – EdStevens Apr 12 '20 at 17:25
  • I agree Ed. I just added some more points as they are important too. – Lalit Kumar B Apr 12 '20 at 18:00
  • 1
    Very true, Lalit. We're not at cross-purposes here. You gave the OP the technical fix and reasoning. I was merely making the point (really, directed to the OP) that this entire issue flows from not learning lessons of 20 years ago. It's increasingly apparent that somehow those lessons are not being passed on to the next generation. I would hope that as they re-learn it, what they learn is the fundamental of don't ever use 2-digit years. But I'm losing hope on that front as well, as I continue to see reliance on short-cuts. – EdStevens Apr 12 '20 at 20:32
  • You're absolutely right, Ed. You made a very good point that the lesson we learnt in the late 90s didn't pass along to next generation. 20 years back it was a nightmare when the consequnces of using 2 digit years dawned upon the engineers and costed millions to fix the systems. This generation is inclined more towards shortcuts and have no clarity on fundamentals. – Lalit Kumar B Apr 12 '20 at 20:58