>- see footer for list info -< Hi everyone, Yes I have learn't that unambiguous date formats are evil (as well as Access). I did have to amend the search form where users enter dates so that it now creates dates in d/mmm/yyyy format. I also found that my query was wrong as well.
So now my dates are being passed in a ambiguous format, turn into dates using CreateODBCDate and successfully being used in my query. Thanks a lot for all the help. Stephen On 31/01/06, Gary F <[EMAIL PROTECTED]> wrote: > > >- 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 > -< > _______________________________________________ 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 -<
