Home » SQL & PL/SQL » SQL & PL/SQL » Date compare (Oracle RDBMS, 10g, Linux)
Date compare [message #331273] Wed, 02 July 2008 17:41 Go to next message
gkornacki
Messages: 6
Registered: January 2007
Location: Florida
Junior Member
Can anyone tell me why this produces what it does?

select count(*)
from table
where to_char(timestamp_datefield, 'mmddyyyy') < '01042008'

I get 2652

Where the following gives me.

select count(*)
from table
where to_char(timestamp_datefield, 'yyyy') = '2007'

I get 345264

Am I missing something?

My goal is to write a stored proc to delete records from table that are over 180 days old and this is kind of baffleing me. It should be pretty simple.
Re: Date compare [message #331274 is a reply to message #331273] Wed, 02 July 2008 17:55 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>Can anyone tell me why this produces what it does?
Because strings are not date datatype.

String have strict collating sequence.

Below might give more expected results.

select count(*) from table
where to_char(timestamp_datefield, 'yyyymmdd') < '20080104'
Re: Date compare [message #331275 is a reply to message #331273] Wed, 02 July 2008 18:05 Go to previous message
gkornacki
Messages: 6
Registered: January 2007
Location: Florida
Junior Member
Thanks. That explains it. It also explains why when I used trunc(timestamp_datefield) I got what I wanted too. And thanks for the quick response too.

[Updated on: Wed, 02 July 2008 18:05]

Report message to a moderator

Previous Topic: Capturing SQL statements to a log table.
Next Topic: Bulk collect for 8i
Goto Forum:
  


Current Time: Mon Dec 05 19:30:06 CST 2016

Total time taken to generate the page: 0.10068 seconds