LESS THAN OR EQUAL TO IN Oracle SQL

Question

updated_date  = 08-Jun-2010;

I have a query like this

select * from asd whre updated_date <= todate('08-Jun-2010', 'dd-MM-yy');

but I am not getting any results. it only works if todate is 09-Jun-2010...

i.e. my equalto operator is not working properly.

why is it like that?

Answer 1

Check this,

select to_date('08-Jun-2010', 'dd-MON-yyyy') from dual;

It is equal to 2010-06-08 00:00:00. Notice the time.

The updated_date has time portion. To include them, please use this query,

select * from asd where trunc(updated_date) <= to_date('08-Jun-2010', 'dd-MON-yyyy');

The default TRUNC function for date parameter will remove the time.

Refer Trunc

Answer 2

In Oracle a DATE is a point in time. It always has a time component with a precision to the second. todate('08-Jun-2010', 'dd-Mon-yyyy') is in Oracle the same as todate('08-Jun-2010 00:00:00', 'dd-Mon-yyyy hh24:mi:ss'). So if you select rows up to that date you won't get any row on that day with a time component not equal to 00:00.

If you want to select all the rows up to and including 08-JUN-2010, I would suggest using:

< to_date('09-06-2010', 'dd-MM-yyyy')

or

<= to_date('08-06-2010 23:59:59', 'dd-MM-yyyy hh24:mi:ss')

Note - I corrected your date format: you need to use MON if you want to use the abbreviated month name. I would suggest using MM instead, so that you won't get error when someone changes its client settings (NLS_DATE_LANGUAGE). Also prefer the use of YYYY instead of YY.