> Im trying to convert some existing access apps from using ODBC
> to OLEDB, only theres an issue with the ODBC date format ( {ts
> '2000-07-06 14:34:00} ). How do I properly escape/format a date/time
> value in an OLEDB query?
Last year I did a ODBC to OLE conversion for an Access '97 database
and posted some caveats to the list on my experience. This portion of
the original post deals with the dates. I basically figured it out by
trial and error.
Dates with Access & OLE
---------------------------------
Since I couldn't pass ODBC dates, I used something like:
<cfset DateTimeStamp = DateFormat(now(), "MM/DD/YYYY") & " " &
TimeFormat(now(), "h:mm:ss tt")>
To pass the variable in an SQL statement:
Values(#widget_ID_number#, '#widget_name#', '#DateTimeStamp#')
It was pointed out to me that you could also pass the date using the
Access date() and time() functions in an SQL statement, so I now use:
Values(#widget_ID_number#, '#widget_name#', Date() & ' ' & Time())
In Access you need to set the default for the field to "=now()".
So, for easy date entry in Access 97 using OLE, simply use the date()
function in your SQL statements. I think this also applies to SQL
Server & OLE using the getDate() function.
To use a form date in an SQL query:
<cfif FORM.DateStart IS NOT "">
<cfset startDate = DateFormat(FORM.DateStart,'DD/MM/YYYY')>
AND (A.testCompDate >= DateValue(#DE(startDate)#))
</cfif>
Using the date in SQL search queries I recall that Access was
particluar about the date format, but I can't remember the details.
The DD/MM/YYYY format worked for me, so I'm guessing that perhaps it
was the date format MM/DD/YYYY that didn't work.
Hope this helps.
Mark Zukiwsky
Edmonton, Canda
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
