Re: oracle date problem

Well, given that DTTM is of type DATE:

The first query fetches the right number of rows by chance. select count(*) from table where DTTM > '10-Jan-06'; uses oracle's default date format string, which means that is is the same thing as saying to_date('01/10/2006 00:00:00','mm/dd/yyyy hh24:mi:ss') if you always want to truncate to midnight, and you want to tie your code to oracle's default date format, this probably won't break too often. Of course, when it does, it will take more time than you think.

The second query is bad, bad, bad. put it out of mind.

The third query -- so close, so close, any yet so far. what you have is:
where to_date(DTTM, 'yyyy-mm-dd hh24:mi:ss')

> to_date('2006-01-10 12:35:44', 'yyyy-mm-dd hh24:mi:ss');

what you need is:
where DTTM > to_date('2006-01-10 12:35:44', 'yyyy-mm-dd hh24:mi:ss');

DTTM is already a date; you don't need to convert it. It's returning no rows because DTTM will return 22-FEB-2006, so you get where to_date('22-FEB-2006','yyyy-mm-dd hh24:mi:ss') > ... Received on Thu Feb 23 2006 - 22:38:16 CST