0

I am using below queries.

select sysdate from dual where to_char(sysdate,'DD-MM-YY')='28-MAY-13';

and the output is null. But for below query, output is current date.

select sysdate from dual where to_date(sysdate,'DD-MM-YY')= to_date('28-MAY-13','DD-MM-YY');

Why the to_char function can not return the sysdate?

Shouln't we use to_char to compare date?

Samurai
  • 843
  • 6
  • 23
  • 44
  • 1
    note that `to_date(sysdate,'DD-MM-YY')` does an implicit conversion of a date (`sysdate`) to a string, i.e. `to_date(to_char(sysdate),'DD-MM-YY')` – Jeffrey Kemp May 29 '13 at 02:07

2 Answers2

4

Regarding: Shouln't we use to_char to compare date?

No we should not. We should use to_char to format a date for display purposes. If you have any other reason in mind, it's probably a bad idea.

When comparing dates in oracle, bear in mind that oracle dates include times. The best way to see if a value falls within a date range is:

where myDateField >= date1
and myDateField < the day after date2

When your date range is today, do this:

where myDateField >= trunc(sysdate)
and myDateField < trunc(sysdate + 1 )

If you want to compare it to a input parameter that is a string, use to_date to convert the string to a date, and then implement the same general idea.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43
3

Try 'DD-MON-YY' format to map to '28-MAY-13'

'MM' maps to the month number.

EDIT: SQL can figure out to use the correct format in to_date; it automatically converts 'DD-MM-YY' to 'DD-MON-YY' when it sees the input string is in '28-MAY-13' format. to_char does not make any assumptions, however; so it is trying to compare '28-05-13' to '28-MAY-13'

EDIT2: An added note is that DUAL can only ever return one row; so you could just do 

 SELECT sysdate FROM DUAL

Dan Bracuk has some good points about date comparison; when possible keep them in date format. Use trunc(DateTime) if only the day matters and not the time; this is usually necessary if you use '=' but often not necessary if you do check for BETWEEN

ZeroK
  • 378
  • 3
  • 9