There are three CreateODBC functions, CreateODBCDate(), CreateODBCDateTime(),
and CreateODBCTime(). Which one you use depends a little bit on the type of
field (date, time, or date/time) and also what kind of comparison you need to
do. By using CreateODBCDate(), you create an ODBC object that will usually be
treated as "midnight at the beginning of" the date created.
For example, say StartVisit contains the date "May 8, 2000". If your database
date/time column contains time values, the following would retrieve all records
with StartDate on or after midnight at the start of May 8, 2000.
SELECT *
FROM WebSite
WHERE StartDate > #CreateODBCDate(StartVisit)#
To be on the safe side, use >=. I know Access allows you to store just date
values in date/time fields. So, if you stored values without a time portion,
you'd have to use the >= to catch items ON May 8, 2000.
SELECT *
FROM WebSite
WHERE StartDate >= #CreateODBCDate(StartVisit)#
Jim
-----Original Message-----
From: Paige Chandler <[EMAIL PROTECTED]>
To: CF-Talk <[EMAIL PROTECTED]>
Date: Saturday, January 20, 2001 10:04 PM
Subject: Re: Selecting on dates
>Hi Jim,
>
>I've used 'GT' in other Select statements successfully - but I'm not married
>to it. The problem is that the field in the table is in the Date/Time format
>(mmddyyhhmm) Hence I was trying to use the
>#DateFormat(StartVisit,'mm-dd-yy')# to extract the portion of just the date
>for the comparison. It works for display but I can't find any examples of
>using hoe to use it, or something better, for comparison. Thanks.
>
>Regards,
>
>Paige
>----- Original Message -----
>From: "Jim McAtee" <[EMAIL PROTECTED]>
>To: "CF-Talk" <[EMAIL PROTECTED]>
>Sent: Saturday, January 20, 2001 11:13 PM
>Subject: Re: Selecting on dates
>
>
>> It might be safer to use an ODBC formatted date. Depends on the database.
>Also,
>> I'm not sure what happens to your 'GT' operator (does CF translate it?),
>but SQL
>> usually uses operators like >, >=, <, <=, =, <>.
>>
>> SELECT *
>> FROM WebSite
>> WHERE StartDate > #CreateODBCDate(StartVisit)#
>>
>>
>> Jim
>>
>>
>>
>> -----Original Message-----
>> From: Paige Chandler <[EMAIL PROTECTED]>
>> To: CF-Talk <[EMAIL PROTECTED]>
>> Date: Saturday, January 20, 2001 9:03 PM
>> Subject: Selecting on dates
>>
>>
>> >Hi All,
>> >
>> >The following function works great for displaying a date, but StartVisit
>isn't
>> found when trying to compare it in a Select statement. I'd really
>appreciate an
>> example of the correct syntax to use. TIA.
>> >
>> >Regards,
>> >
>> >Paige
>> >
>> >Select *
>> >From WebSite
>> >WHERE StartDate GT #DateFormat(StartVisit,'mm-dd-yy')#
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
