Re: oracle date problem

Notes Inline...

krish wrote:
> I have a query which has a date comparision in the where clause.
> 1. select count(*) from table where DTTM > '10-Jan-06';

This is the 'correct' query and it tells us that it is probable that the default for nls_date_format is being used 'DD-MON-YY'

There is implicit date conversion occuring so the last part of the predicate actually reads '10-Jan-06 00:00:00' (exactly midnight on 10 January, 2006).

> 2. select count(*) from table where to_char(DTTM) > to_char('2006-01-10 12:35:44');

So, knowing the date format, we also know that the values of these 'strings' is not the same as above. In fact, in the second part of the predicate TO_CHAR of a character string is unneeded. It will take the TO_CHAR of DTTM and compare it to a character string.

Without a format mask, you will default to the NLS_DATE_FORMAT, which is 'DD-MON-YY' and then compare two strings that do not have the same meaning.
Even if you did use the correct format mask in the second TO_CHAR, one of the big differences in this query v. query 1 is that the second query excludes all records with a DTTM between 00:00:00 and 12:35:44. That could account for the difference in the queries.

> 3. select count(*) from table where to_date(DTTM, 'yyyy-mm-dd
> hh24:mi:ss') > to_date('2006-01-10 12:35:44', 'yyyy-mm-dd hh24:mi:ss');

Hmmm...not quite sure why there are not any rows being returned. Without seeing a sample of the exact data, I'm not seeing it (though I am sure someone else in cdos land does).

The bottom line (at least for me) is that each of the queries is sufficiently syntactically different that they cannot be compared. Date comparisons in Oracle can be a little tricky.

Regards,
Dan Fink Received on Wed Feb 22 2006 - 14:42:18 CST