Re: date comparison

"Moritz Klein" <mklein_at_students.uni-mainz.de> wrote in message news:dumpt3$bqn$1_at_news1.zdv.uni-mainz.de...
> Raj schrieb:
>
>> I need to compare two dates...one stored in a table and the other user
>> generated.
>
>> The table column time is of the format 08/03/2006 07:31.
>
> A date stored in the database is always in internal date format. It does
> not matter how it is put into. You do have defined the column as date?
>
>> The user generated time is 2006/03/08.
>> I am currently comparing them like this:
>> ...
>> WHERE to_date(to_char(time, 'yyyy/mm/dd'), 'yyyy/mm/dd') =
>> to_date('2006/03/08', 'yyyy/mm/dd')
>> ...
>
> If time is a date-field use this one:
> where time = to_date(<userinput>, 'yyyy/mm/dd')
> if time is not a date-field change it.

Ah, I have found out what my problem is!

My time column is a date in the format 08/03/2006 07:31. When I convert the <userinput> date into 'yyyy/mm/dd' format, it seems to be comparing, for example, '08/03/2006 07:31' against '08/03/2006 00:00' and so I get no rows returned.

Is there a simple way to get the time column to compare only the '08/03/2006' portion? There are millions of rows in the table!

Thanks again,
Raj Received on Wed Mar 08 2006 - 09:35:21 CST