Re: oracle date problem

"krish" <vardhviyasa_at_gmail.com> wrote in message news:1140637793.858769.241380_at_g43g2000cwa.googlegroups.com...
> 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
>

You need to specify the date format instead of allowing an implicit and prone to error conversion.
to_date('10-Jan-06','dd-mmm-yy') which really should be to_date('10-Jan-2006','dd-mmm-yyyy')
Much better to spicify the 4 digit year and not 2 digits. (2 digit years are valid years and probably not what you want. eg the year 6 AD) Jim Received on Wed Feb 22 2006 - 20:10:30 CST