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:1750
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:>
