On 9/14/00, W Luke penned:
>Hi,
>
>I have a Date/Time column in a Table (called expiry_date), with dates in it
>such as 30/09/2000
>
>I need to run some SQL on it as shown below - but it's not returning any
>results. What is the correct method of searching for dates in SQL?
>
>SELECT body, expiry_date FROM advert_details WHERE date = '20/09/2000'
First, drop the single quotes.
WHERE date = #createodbcdate('20/09/2000')#
That will work, and will enter into the database: {d '2000-09-20'}
BUT!!! #createodbcdate('09/20/2000')# will also enter {d '2000-09-20'}
What I'm getting at is, given a choice, ColdFusion will read the date
as MM/DD/YYYY. It's smart enough to know that if either of the first
2 date parts are greater than 12, it must be the month. And that if
BOTH are larger than 12, it's not a date. But, if they are both 12 or
less, it will read the first part as the month.
So, while all the way down to Sept. 13th (13/09/2000 your way),
you'll be OK, as soon as your date reads 12/09/2000, CF will read it
as December 9th and not Sept. 12th.
So, #createodbcdate('MM/DD/YYYY')# is your best bet. :)
--
Bud Schneehagen - Tropical Web Creations
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
ColdFusion Solutions / eCommerce Development
[EMAIL PROTECTED]
http://www.twcreations.com/
954.721.3452
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
