>- see footer for list info -< You could, but potentially you will have the same problem with 2006-03-03 As either could be the month or day. Unles su store it in the database in that format.
Russ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Duddell Sent: 31 January 2006 17:03 To: Coldfusion Development Subject: RE: [CF-Dev] Filtering using dates >- see footer for list info -< ... or you can use the ISO8601 standard of YYYY-MM-DD ... http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html http://www.w3.org/International/questions/qa-date-format ________________________________ From: Snake [mailto:[EMAIL PROTECTED] Sent: 31 January 2006 13:46 To: 'Coldfusion Development' Subject: RE: [CF-Dev] Filtering using dates >- see footer for list info -< Gary, I have always always told everyone with similar post that same thing Gary, use d/mmm/yyyy format, by sticking to that rule I have never ever had any problems with dates, or month and day being swapped round. But some people just like to make things difficult for themselves and insist in using LSDateFormat() and Locale functions and converting ODBCDates into strings, and back again, etc etc, aggghhhh. And nothing you say will make any difference. Then you watch as the thread drags on, with all this code tweaking getting more convoluted. Russ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Gary F Sent: 31 January 2006 11:34 To: Coldfusion Development Subject: Re: [CF-Dev] Filtering using dates >- see footer for list info -< Whenever you do date operations it's always safer to handle them in a raw format (ODBCdateFormat) or in an undisputable friendly format such as 2-Mar-05 which can't be mistaken for 3/2/05. In your code always use a raw format and just use friendly formats for presentational purposes only as it's easy for Access or ODBC to mix up UK/US formats. You need to send the raw CreateODBCdateFormat date to Access, not the LSDateFormat that you created earlier in your code. You also need to look at what format startDate is in when it's handed over to your CFC. Ideally it should be converted to ODBCformat immediately to reduce the risk of UK/US confusion. Try to get your date selector to create the date as d/mmm/yy (2/mar/05) to limit the risk right from the source. Gary. On 1/31/06, Stephen Adams <[EMAIL PROTECTED]> wrote: > > >- see footer for list info -< > Hi, > > I'm trying to run a query against an Access DB, with dates in UK > format, but I keep getting Type mismatch in expression error messages. > > In my application the user selects two dates from a Flash Form date > time field, these dates are then passed into a cffunction as > arguments. In the function I set the passed arguments as dates using > the CreateODBCDate funciton, like this: > > <cfset start_date = #LSDateFormat(CreateODBCDate(arguments.startDate > ),'dd/mm/yyyy')#> > <cfset end_date = > #LSDateFormat(CreateODBCDate(arguments.endDate > ),'dd/mm/yyyy')#> > > I also format the dates. Then in my query I'm trying to use a BETWEEN > to get a list back. Here's my query: > > <cfquery name="qGetDataExtract" datasource="#variables.DSN#"> > SELECT q.QueryID AS QueryID, > q.DRB_Number AS DRB_Number, > q.Name AS Name, > q.Contact AS Contact, > q.TimeOfQuery AS TimeOfQuery, > q.DateOfQuery AS DateOfQuery, > a.QueryActionDesc AS CallAction, > b.QueryTypeDesc AS QueryType > FROM tbl_Queries q, > tbl_QueryActions a, > tbl_QueryType b > WHERE q.DateOfQuery BETWEEN #start_date# And #end_date# > AND a.QueryActionID = q.CallType > AND b.QueryTypeID = q.QueryTypeID > </cfquery> > > My dates in the Access table are being saved as Date/Time fields, with > a format of Short Date and an Input Mask of dd/mm/yyyy. > > All the examples I've seen use CreateODBCDate and seem to work, but a > lot are in US date format. Can anyone show me what I need to do to fix > this problem in my code? > > Thanks > > Stephen > _______________________________________________ > > For details on ALL mailing lists and for joining or leaving lists, go > to http://list.cfdeveloper.co.uk/mailman/listinfo > > -- > CFDeveloper Sponsors:- > >- Hosting provided by www.cfmxhosting.co.uk -< > >- Forum provided by www.fusetalk.com -< > >- DHTML Menus provided by www.APYCOM.com -< > >- Lists hosted by www.Gradwell.com -< > >- CFdeveloper is run by Russ Michaels, feel free to volunteer your > >help > -< > _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help >-< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -< ************************************************************************ **** Notice: This e-mail and any attachments are confidential and may contain legally privileged information and/or copyright material of Hansen Technologies Limited or third parties. Copying, distributing, disclosing, commercialising or otherwise acting in reliance on this e-mail and any attachments is strictly prohibited unless you are the addressee of this e-mail and have written permission to do so. If you have received this e-mail in error please delete this e-mail (including any copies and attachments) and contact Hansen Technologies Limited by return e-mail or by telephone on + 61 39840 3000. Any views expressed in this e-mail are those of the individual sender and may not necessarily reflect the views of or be a commitment by the organisation, except where the individual sender has the authority and expressly states them to be so. ************************************************************************ **** _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help >-< _______________________________________________ For details on ALL mailing lists and for joining or leaving lists, go to http://list.cfdeveloper.co.uk/mailman/listinfo -- CFDeveloper Sponsors:- >- Hosting provided by www.cfmxhosting.co.uk -< >- Forum provided by www.fusetalk.com -< >- DHTML Menus provided by www.APYCOM.com -< >- Lists hosted by www.Gradwell.com -< >- CFdeveloper is run by Russ Michaels, feel free to volunteer your help -<
