"Terry Dykstra" <tddykstra_at_forestoil.ca> wrote in message
news:PXCPf.15347$Cp4.12500_at_edtnps90...
> "Raj" <raj.kothary_at_thus.net> wrote in message
> news:dumtna$lgp$1$8302bc10_at_news.demon.co.uk...
>> "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
>>
>>
> You use trunc(datecol) to set the time portion to 0:0
Thanks. I actually had to get the values for the whole day so I have used
code to create a 2nd date and used the between function instead. This works
very quickly since the time column does not have to be modified in any way.
Thank you for your suggestions...it has helped me discover what the main
issue was.
Kind regards,
Raj
Received on Wed Mar 08 2006 - 09:55:31 CST
