If you use createODBCDateTime() it will create a timestamp, which is
generally the format you should use all throughout the application (or
datestamp -- createODBCDate() ): Only going to other formats for display.
This solves all sorts of date/time issues as it's a distinct standard
format... not variable w/ peoples prefference and locale like 'dd-mm-yy',
etc.
I'd suggest you not use GT and the like as ANSI SQL calls for the =, >,
>=, <, <=, <> operators, so while it may work in your current db it's not
very portable.
If you're worrying about the field having more precision than the passed
date, don't. If you pass it in datestamp or timestamp version the dbms will
work w/ it. If you create a timestamp using only yyyy.mm.dd then the time
will read 00:00:00 of that day (24h clock); if you passed a datestamp and
the db field had the precision of seconds, the dbms will handle it the same.
In conclusion, dateFormat() should only be used when outputting to a user
(or log file w/ set format). Instantly convert all date/times to a timestamp
or datestamp object and use that format exclusively inside your application.
You shouldn't have any date/time problems again.
-----Original Message-----
From: Paige Chandler [mailto:[EMAIL PROTECTED]]
Sent: January 21, 2001 00:09
To: CF-Talk
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
