getting a date (odbc?) 1

[leadman]

i want to capture the curent server time and insert it into a database field as part of a query so i added the line:
<cfset surveydate = CreateODBCDateTime(Now())>

just before the query which contains:
insert into table (surveydate) values ('#surveydate#')

In my access database i created a field called &quot;surveydate&quot; and gave it a date/time datatype. When the query runs i get this error:
ODBC Error Code = 37000 (Syntax error or access violation)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression ''{ts '2001-07-17 03:50:46'}''.
The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (98:1) to (98:157).

The right date is in there but i have that 'missing operator' - any ideas?

 

[strantheman]

hi leadman

Remove the quotes. What a coincidence you surrounded the error in the error. 'missing operator'

Heh. If a date is in ODBC format, access, sql, and most if not all ansi 92 db software will take it as {ts '2001-07-17 03:50:46'} . For some, '#dateFormat(now(),&quot;mm/dd/yyyy&quot#' is preferable, but your method is the most standard and compatible.

insert into table (surveydate) values (#surveydate#)

 

[leadman]

that did it - thanks!

 

[plook]

Hi !

I am looking to write the date/time in a DB field too, so I grabbed the info written here... However, I am getting the following error message when I use &quot;CreateODBCDateTime(Now()) :

ODBC Error Code = 37000 (Syntax error or access violation)

[Microsoft][ODBC SQL Server Driver][SQL Server]'Now' is not a recognized function name.

Any ideas what I should do ?

Thanks

 

[calista]

Hey, plook!

Probably a syntax error of some kind. Post your code, and I'm sure someone will help you out. Calista :-X
Jedi Knight,
Champion of the Force

 

[strantheman]

hi plook

CreateODBCDateTime(Now()) is a function for ColdFusion.

To make sure this is evaluated before the SQL is sent to SQL server, you have to wrap the function in ##.

#CreateODBCDateTime(Now())#

If this doesn't fix it post the code.

good luck

 

[leadman]

thanks guys - i think i got it. In my case i had the field in access defined as a date field. It wouldnt take '#surveydate#' as such unless it was set as text. It is taking #surveydate# though. Thanks for the ideas - they are a great learning tool.