Oracle to_date SQL formatting issue,

Question

I am formatting time stamp field to '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 is returning 0 count.

select count(*) from task_log where to_date (event_dt,'DD/MM/YYYY')= to_date('19/05/0015','DD/MM/YYYY');

The above query is returning 29 count.

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

and so on...

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 to 0015?

How to avoid formatting year to 0015 format? I need to my year in 2015 format. Please help me to fix this issue.

Answer 1

to_date (event_dt,'DD/MM/YYYY')

Based on your reply to the comments,

1. Your database is Oracle
2. Your event_date column's data type is DATE.

Never apply TO_DATE on a DATE column. It forces Oracle to:

• first convert it into a string
• then convert it back to date

based on the locale-specific NLS settings. You need TO_DATE to convert a literal into date. For date-arithmetic, leave the date as it is.

All you need to do is:

select count(*)
   from task_log 
  where trunc(event_dt) = to_date('19/05/2015','DD/MM/YYYY');

Now, the catch is the TRUNC function. Remember, a DATE has both the datetime elements. So, you must keep in mind the time portion while comparing two dates.

Applying TRUNC would suppress any regular index usage. It is better to use a 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;