Re: oracle date problem

krish wrote:
> 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 ???

Run "desc table" and post the results. Without knowing whether DTTM is defined as a date, varchar2, etc. we can't answer your question. Also, include your Oracle version and OS. But, even without that info, it's clear that the three queries will not return identical results.

Generally, you don't want to store dates as characters, and you don't want to compare dates by comparing them to strings. You also don't want to assume some particular date format, since it can change and break your code. You also don't want to use two digit years. ( Didn't we learn anything six years ago?)

You *do* want to store date-time info as the date datatype and use the built in date funtctions and comparitors to manipulate and compare them.

i.e. You reallly want to use a query that looks something like this:

select count(*) from table where DTTM > to_date('10-Jan-2006','dd-Mon-yyyy');

Hopefully DTTM is a date data type. Otherwise, you're screwed.

//Walt Received on Wed Feb 22 2006 - 14:55:11 CST