CODEWITHSUNDEEP
sqloracle-databasedateformaty2k
Sudan IS
Sudan IS

Reputation: 43

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?

Upvotes: 4

Views: 278

Answers (3)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

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'

Upvotes: 0

vc 74
vc 74

Reputation: 38179

TO_DATE('21-09-89','DD-MM-YY')

evaluates to Sep 21 2089

Upvotes: 3

Popeye
Popeye

Reputation: 35910

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

Upvotes: 8

Related Questions