We have had many problems with this over the years, the main one being American locale settings on the server so if you pass 07/08/2004the DB thinks it is July 08, 2004 not 07 August, 2004. The best way I have found that has never failed yet in SQL server 7, 2000 and MS Access is to use DateFormat(yourdate, 'dd-mmm-yyyy') other users of this list may have different experience but I will not be easily swayed.
Ellis C Wood BSc Ellwood Web Solutions T: 01623 459973 E: [EMAIL PROTECTED] W: http://www.ellwoodwebsolutions.co.uk -----Original Message----- From: Lovelock, Richard J [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 10:17 To: '[EMAIL PROTECTED]' Subject: RE: [ cf-dev ] anyone spot what's wrong with this? sorry to partially hijack....i've had loads of fun and games with dates this week (not the romantic kind unfortunately) how do you differentiate when to use CreateODBCDate and using DateFormat and how do they differ in their result? I ended up using both I think before I could get query to work properly but I still didn't fully understand why? and there have been different recommendations in reply to this thread!? _______________________________________________________ * Regards, Richard Lovelock, Senior Application Analyst. Westminster City Council - Web Support Cap Gemini Ernst & Young Southbank 95 Wandsworth Road London SW8 2HG ( 0870 906 7482 _______________________________________________________ -----Original Message----- From: Ellwood [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 10:13 To: [EMAIL PROTECTED] Subject: RE: [ cf-dev ] anyone spot what's wrong with this? Back to basics lads. The datatime needs to be in single quotes for a start. Secondly, is it an access db because if so dates need to be in #'s so you will need to triple escape them i.e. ###data### and thirdly, the only guaranteed way to pass dates and not confuse the locale settings of the db is to use dateformat(yourdate, 'dd-mmm-yyyy') Ellis C Wood BSc Ellwood Web Solutions T: 01623 459973 E: [EMAIL PROTECTED] W: http://www.ellwoodwebsolutions.co.uk -----Original Message----- From: Stephen Moretti [mailto:[EMAIL PROTECTED] Sent: 17 March 2004 10:04 To: [EMAIL PROTECTED] Subject: Re: [ cf-dev ] anyone spot what's wrong with this? You need to pass ODBC Dates rather than 'Friday October 1, 2004' Use CreateODBCDate() function in your query. You might need to create a real date from the text you get for your date using CreateDate() STephen Ian Westbrook (FDM) wrote: > HI > can anyone spot what's wrong with this? this is the error message... > > Something just went wrong on ArtSouthEast. Below is the error > information. Sort it aaaaaaht! > ODBC Error Code = 37000 (Syntax error or access violation)<P> > [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing > operator) in query expression 'diary.directoryid = directory.id > AND directory.town = towns.id > > AND directory.town = 148 > > AND diary.artform = 35 > > AND > (( diary.startdate >= Friday October 1, 2004) > OR > ( diary.enddate >= Friday October 1, 2004)) > AND diary.publish = 'ye'.<P><P>SQL = "SELECT diary.id AS diaryid, > diary.directoryid, diary.artform AS diaryartform, diary.*, directory.id > AS directoryid, directory.fullname, directory.town, towns.id AS townid, > towns.town AS town > FROM diary, directory, towns > WHERE diary.directoryid = directory.id > AND directory.town = towns.id > > AND directory.town = 148 > > AND diary.artform = 35 > > AND > (( diary.startdate >= Friday October 1, 2004) > OR > ( diary.enddate >= Friday October 1, 2004)) > AND diary.publish = 'yes' > ORDER BY diary.startdate, diary.enddate, diary.title"<P>Data Source = > "ARTSOUTHEAST"<P><p>The error occurred while processing an element with > a general identifier of (CFQUERY), occupying document position (360:2) > to (360:45) in the template file > D:\ARTSOUTHEAST\HTDOCS\INCLUDES\DIARY.CFM.</p> > > D:\ARTSOUTHEAST\HTDOCS\DIARY.CFM > > > TIA > > Ian W > > -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED] -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED] ======================================================= This message contains information that may be privileged or confidential and is the property of the Cap Gemini Ernst & Young Group. It is intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorised to read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message in error, please notify the sender immediately and delete all copies of this message. ======================================================= -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED] -- These lists are syncronised with the CFDeveloper forum at http://forum.cfdeveloper.co.uk/ Archive: http://www.mail-archive.com/dev%40lists.cfdeveloper.co.uk/ CFDeveloper Sponsors and contributors:- *Hosting and support provided by CFMXhosting.co.uk* :: *ActivePDF provided by activepdf.com* *Forums provided by fusetalk.com* :: *ProWorkFlow provided by proworkflow.com* *Tutorials provided by helmguru.com* :: *Lists hosted by gradwell.com* To unsubscribe, e-mail: [EMAIL PROTECTED]
