>- see footer for list info -< Russ, It's been quiet for the past hour so maybe Stephen has realised the mistake of using an unambiguous date format. I went through this phase with dates years ago and it was a painful experience but an important lesson. Even now when working with SQL Server & Oracle I always use a full date format on both client and server. It's zero extra effort and saves an untold number of problems and problems resulting from those problems! :-)
Shame mailing lists can't do stickies! ;-) Gary On 1/31/06, Snake <[EMAIL PROTECTED]> wrote: > > >- 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 > -< > _______________________________________________ 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 -<
