CODEWITHSUNDEEP
oracle-databasedatecomparison
Hatjhie
Hatjhie

Reputation: 1365

How to compare date in string with sysdate in Oracle?

I am new with Oracle and still get used to SQL Server.

I do not understand on why the command below would return '1' when clearly sysdate which is today is 29-Apr-15 that clearly before 30-Apr-15

It should return 0. But I do not understand why it returns 1.

Any ideas? Thanks =)

SELECT CASE WHEN sysdate > to_date('30-Apr-15','DD-MON-YYYY') THEN '1' ELSE '0' END
    FROM DUAL

Upvotes: 2

Views: 11693

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49122

Because you have used 15 instead of 2015. You need to be aware of the Y2K bug. Using 'YY' format for year in a DATE is a bad idea. Always try to use 'YYYY' when converting a string into DATE

Or else, use the RR format to overcome the Y2K issue.

For example,

SQL> alter session set nls_date_format='mm/dd/yyyy';

Session altered.

SQL> SELECT TO_DATE('30-Apr-15','DD-MON-RR') RR,
  2         TO_DATE('30-Apr-15','DD-MON-YYYY') YY
  3  FROM DUAL;

RR         YY
---------- ----------
04/30/2015 04/30/0015

SQL>

Using the same in your query:

SQL> SELECT
  2    CASE
  3      WHEN sysdate > to_date('30-Apr-15','DD-MON-RR')
  4      THEN '1'
  5      ELSE '0'
  6    END
  7  FROM DUAL
  8  /

C
-
0

SQL>

Upvotes: 2

davegreen100
davegreen100

Reputation: 2115

you're missing the century from your date

SELECT CASE WHEN sysdate > to_date('30-Apr-2015','DD-MON-YYYY') THEN '1' ELSE '0' END
    FROM DUAL

Upvotes: 3

Related Questions