Maybe I'm missing something, but if we're talking about CF5 access only through the MyODBC driver (maybe we're not) then why not just use ODBC formatted dates & date/times?
----- Original Message ----- From: "Barney Boisvert" <[EMAIL PROTECTED]> To: "CF-Talk" <[EMAIL PROTECTED]> Sent: Wednesday, July 09, 2003 11:47 AM Subject: RE: CF5 to MySQL without ODBC > Yeah, that'll work on both 3.x and 4.x, assuming you get the leading > apostrophe on the second number in there ; ) > > In 3.x omitting the quotes would also work, but not in 4.x, unless it's a > date column (rather than datetime). The reason is that they are protecting > the numeric input as a means for dealing with timestamp columns (which are > stored numerically), and datetime and date columns (stored as strings) use > string values. I'd recommend using a formatted string to make it 100% clear > that it is a character string you're passing, rather than number that > happens to be in quotes: > > where datefield between '2002:07:09 00:00:00' and '2003:07:09 00:00:01' > > The delimiter used is inconsequential, you can use pretty much any > punctuation character, and you can use several in a single value (2003:07/09 > 00+00-00). I use colons throughout for consistency, but that's personal > preference. > > --- > Barney Boisvert, Senior Development Engineer > AudienceCentral > [EMAIL PROTECTED] > voice : 360.756.8080 x12 > fax : 360.647.5351 > > www.audiencecentral.com > > > > -----Original Message----- > > From: Matt Robertson [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, July 09, 2003 10:04 AM > > To: CF-Talk > > Subject: RE: CF5 to MySQL without ODBC > > > > > > Thanks Barney. Very useful info. I have never been fully comfortable > > with mySQL dates but if that's the underlying way to manually construct > > then my problems are solved. I've been using straight numeric fields in > > some cases with a yyyymmdd input mask. > > > > Can this be used in SQL queries? i.e. > > > > Where datefield between '20020709000000' and 20030709000000' > > > > -------------------------------------------- > > Matt Robertson [EMAIL PROTECTED] > > MSB Designs, Inc. http://mysecretbase.com > > -------------------------------------------- > > > > -----Original Message----- > > From: Barney Boisvert [mailto:[EMAIL PROTECTED] > > Sent: Wednesday, July 09, 2003 9:46 AM > > To: CF-Talk > > Subject: RE: CF5 to MySQL without ODBC > > > > > > One thing to watch is dates. If you're using datetime columns, you can > > no > > longer specify a date like this: > > > > 20030709 > > > > You have to make it a string, and 14 digits long (to include the time): > > > > '20030709000000' > > > > The recomended format is 'yyyy/mm/dd hh:mm:ss', which is more readable > > than > > the long number. If you specify the former it does not throw an error. > > It > > will consider it an invalid date, and convert it to all zeros, which > > will > > match all or none of your rows, depending on what the comparison is. > > This > > bit me on the rear when I moved to 4.0, although it was simple to fix. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. http://www.cfhosting.com Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
