i think you need to specify the formatting in your statements WHERE TO_DATE(T.DATEENROLCONFIRMED, 'ddmmyyyy') < '05122003'
something like that. the default is DD-MON-YYYY and you were comparing that to DD-MM-YYYY at least thats what I see -----Original Message----- From: frederick valone [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 18, 2003 12:58 PM To: SQL Subject: Re: Oracle: date selection Whats' wrong here? Try changing the where clause to WHERE TO_DATE(T.DATEENROLCONFIRMED) < 05-NOV-2003 OR WHERE TO_DATE(T.DATEENROLCONFIRMED) < '05-NOV-2003' I can't rememner if you need the quotes or not. Thanks, Frederick Michael Kear wrote: >Can anyone tell me what's wrong here? I'm selecting a field that has dates >stored as a string ddmmyyyy and I want to do some comparisons on it >(eventually if date is between XX and YY) but my learning query is giving me >odd results. > >What's happening is it's selecting records, but doesn't care about month. >With the query shown below, it gives an error, saying it got a number and >expected a date. > > > SELECT > TO_DATE(T.DATEENROLCONFIRMED, 'ddmmyyyy') EnrollDate > FROM > Servername.tblinvoicedetails T > WHERE TO_DATE(T.DATEENROLCONFIRMED) < 05-10-2003 > ORDER BY DATEENROLCONFIRMED ASC; > >If I omit the WHERE and ORDER BY clauses, I get a list of dates in the >format 2003-09-29 00:00:00.0 > >If I add the ORDER BY clause, it puts 29 September next to 29 October, and >01 October as lower value than 29 September. In other words it's sorting >them on the original value of ddmmyyyy where of course 29092003 is higher >than 01102003 > >What have I got wrong here? > > >Cheers, >Michael Kear >Windsor, NSW, Australia >AFP Webworks. > > > > > > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Purchase from House of Fusion, a Macromedia Authorized Affiliate and support the CF community. http://affiliates.macromedia.com/t.asp?id=2439&p=go/dr_text_aff1 Message: http://www.houseoffusion.com/lists.cfm?link=i:6:1751 Archives: http://www.houseoffusion.com/lists.cfm?link=t:6 Subscription: http://www.houseoffusion.com/lists.cfm?link=s:6 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=<:emailid:>.<:userid:>.<:listid:>
