oracle date problem

I have a query which has a date comparision in the where clause.

The query looks as

1. select count(*) from table where DTTM > '10-Jan-06';
2. select count(*) from table where to_char(DTTM) > to_char('2006-01-10

12:35:44');

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');

pls tell me whether there will be any difference if I change the date format in the where clause.

I hope there should not be any problem as its all pointing to the same date.

but to my surpraise, each of the query is returning different output.

Q1 is returning 330377

Q2 is returning 1309100

and Q3 is returning 0

The actual return value should be 330377 as returned by Q1.

Can anybody pls let me know the difference ASAP ???

thanks,

Krishna Received on Wed Feb 22 2006 - 13:49:53 CST