4

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.

Improve this question
8
  • mysql or oracle? you have both tag in your question Commented May 19, 2015 at 17:11
  • Queries 1 and 2 are identical how can 1 return 0 and the other 29? Commented May 19, 2015 at 17:14
  • ITs in oracle. Thanks Commented May 19, 2015 at 17:14
  • Queries 1 and 2 are not identical, Query 1 has year 2015, Query 2 year 0015, Thanks. Commented May 19, 2015 at 17:15
  • ah missed that. from this we assume task_log event date is character... can we see structure to confirm that? Hopefully you're not to_dating a date field already just for display purposes. Commented May 19, 2015 at 17:15

1 Answer 1

Reset to default
8

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Its working. Buddy can you please remove ] from query. Thanks for your help and time on this.
@VSRSarmaChinta You're welcome! Actually, there was a lot of information more to be added, and I needed sometime to format everything. Hope it helped you.