Reputation: 3704
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
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
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
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