CODEWITHSUNDEEP
oracle-databaseoracle11g
dhS
dhS

Reputation: 3704

Oracle query not giving result for current_date

What is the query in Oracle to fetch the data for current_date the column end_date is like the following 

end_date
27-10-16 03:35:00.000000000 PM
23-11-16 11:15:00.000000000 AM
02-11-16 03:00:00.000000000 PM
08-11-16 09:00:00.000000000 AM

Like I am running the following query as 

Select * from table1
where end_date < TO_DATE('2017-04-11 00:00:00', 'YYYY-MM-DD HH24:MI:SS')

it is running successfully, but when i replace the query with the current date ... it is not giving the results 

Select * from table1
where end_date < TO_DATE(current_date, 'YYYY-MM-DD HH24:MI:SS')

could someone tell me what is the cause the second query is not giving results.

Upvotes: 0

Views: 803

Answers (3)

dhS
dhS

Reputation: 3704

The query worked like this :

Select * from table1
    where trunc(end_date) < trunc(sysdate)

Trunc is used to compare the both dates and it fetch the results.

Upvotes: 1

Tom J Muthirenthi
Tom J Muthirenthi

Reputation: 3340

CURRENT_DATE is already a DATE value. You can format the output using to_char if you want.

end_date < CURRENT_DATE should do the job. Or you can set the nls parameter accordingly for a better readability.

If you are comparing only date, without timestamp, you can go with trunc()

Upvotes: 0

Nitish
Nitish

Reputation: 1736

CURRENT_DATE returns date. There is no need to use TO_DATE. The below query should be enough.

Select * from table1
  where end_date < current_date;

If you run the below query you'll understand what went wrong for you. Year becomes 0011.

SELECT TO_DATE(current_date, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

Please note that CURRENT_DATE returns the current date in the session time zone. SYSDATE returns the current date and time set for the operating system on which the database resides. This means that CURRENT_DATE and SYSDATE can return different results. You can have a look at this

Upvotes: 2

Related Questions