LESS OR EQUAL IN Oracle SQL

Question

updated_date = 08-Jun-2010; 

I have a request 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 statement is equalto working correctly.

why is that?

Answer 1

In Oracle, DATE is a point in time. It always has a time component accurate to the second. todate('08-Jun-2010', 'dd-Mon-yyyy') is located in Oracle in the same way as todate('08-Jun-2010 00:00:00', 'dd-Mon-yyyy hh24:mi:ss') . Therefore, if you select rows before this date, you will not receive a single row on that day with a time component not equal to 00:00 .

If you want to select all the lines before and enable 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 fixed the date format: you need to use MON if you want to use the abbreviated name of the month. I would suggest using MM instead, so that you don't get an error when someone changes their client settings ( NLS_DATE_LANGUAGE ). Also prefer to use YYYY instead of YY .

Answer 2

Check this,

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

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

updated_date has a temporary part. To enable them, use this query,

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

The default TRUNC function for the date parameter deletes the time.

Refer Trunc