Oracle to_date SQL formatting problem,
I am formatting the timestamp field with "DD / MM / YYYY" format to get all processed records on that date.
select count(*) from task_log where to_date (event_dt,'DD/MM/YYYY')= to_date('19/05/2015','DD/MM/YYYY');
The above query returns 0 score.
select count(*) from task_log where to_date (event_dt,'DD/MM/YYYY')= to_date('19/05/0015','DD/MM/YYYY');
The above query returns 29 samples.
If I run:
select event_dt from task_log where to_date (event_dt,'DD/MM/YYYY')= to_date('19/05/0015','DD/MM/YYYY');
EVENT_DT
5/19/2015 9:35:23 AM
5/19/2015 9:35:23 AM
5/19/2015 9:37:22 AM
5/19/2015 9:37:23 AM
5/19/2015 9:37:32 AM
5/19/2015 9:37:53 AM
etc.
select to_date (event_dt,'DD/MM/YYYY') as to_date_Farmatted from task_log
TO_DATE_FARMATTED
5/19/0015
5/19/0015
5/19/0015
5/19/0015
5/19/0015
The to_date format is formatting year 0015 ?
How to avoid formatting the year to 0015 format? I need my year in 2015 format. Please help me fix this problem.
to_date (event_dt, 'DD / MM / YYYY')
Based on your answer to comments,
- Your Oracle Database
- The data type of the
event_date
DATE column .
Never use TO_DATE on a DATE column . It forces Oracle to:
- first convert it to string
- and then convert it back.
based on locale-specific NLS settings . You need TO_DATE to convert literal to date. For date arithmetic, leave the date as it is.
All you have to do is:
select count(*)
from task_log
where trunc(event_dt) = to_date('19/05/2015','DD/MM/YYYY');
Now the trick is the TRUNC function . Remember that DATE has both datetime elements. So you have to keep the time part in mind when comparing two dates.
Using TRUNC will suppress any normal use of index . Better to use the Date Range condition .
For example,
select count(*)
from task_log
where event_date
between to_date('19/05/2015','DD/MM/YYYY')
and to_date('19/05/2015','DD/MM/YYYY') + 1;
source to share