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.

Hi Moritz,

Yes, time is indeed a date field, however when I do this it returns no rows. This was the first thing I tried. It seems that the format of the date seems to be an issue (although I thought the to_date() function put them into the same internal representation).

There are in fact rows for the query I am running but none are returned unless I massage the date to look the same.

Strange, huh?

Thanks for your help,
Raj

>> This doesn't look very efficient to me though. Can anyone please advise?
>
> It is not very efficient indeed...
>
> hth,
> Moritz
Received on Wed Mar 08 2006 - 09:30:00 CST