Hi Jim,
Thanks for all your help. I'm sure that I need to convert the memory
variable to the same format as the field variable StartVisit (which is in
DateTime). In the CFOutPut code below I can see that the 2 fields are in a
compatible format. But when I try use the comparison in the Where clause I
get an error on STARTDATE. As is I don't get an error on the query but of
course I don't get a valid comparison.
Problem line: WHERE #StartDate2# > #CreateODBCDate(StartVisit)#
Following works, that is no errors:
<CFIF IsDefined("StartDate")>
<CFSET StartDate2 = #CreateODBCDate(StartDate)#>
<CFQUERY NAME="SiteData" DataSource="#Session.WebSite#">
Select *
FROM WebSite
WHERE #StartDate2# > StartVisit
</CFQUERY>
<CFOUTPUT Query="SiteData">
<-- Displays compatible dates-->
#StartDate2#
#CreateODBCDate(StartVisit)#
</cfoutput>
</CFIF>
----- Original Message -----
From: "Jim McAtee" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Sunday, January 21, 2001 1:40 AM
Subject: Re: Selecting on dates
> 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
