ODBC Date w/ SQL Insert

[mmorgensen]

We are working to convert our programs to ODBC dates. I keep getting an error....any help is appreciated

Code::

<CFQUERY DATASOURCE=&quot;#client.coDatabase#&quot;>
INSERT INTO Comments
(LogDate,TicketNumber,Type,UserID,Comments)
VALUES
('#NOW()#','#FORM.TICKETNUMBER#','#FORM.TYPE#','#FORM.USERID#','#FORM.COMMENTs#')
</CFQUERY>


Error::

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

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 4: Incorrect syntax near '2002'.

SQL = &quot;INSERT INTO Comments (LogDate,TicketNumber,Type,UserID,Comments) VALUES ('{ts '2002-12-13 21:44:11'}','10215','Helpdesk Reply','5689590','asdfasdfsadf')&quot;



thank you in advance.

mm

 

[mmorgensen]

BTW: I understand why I am getting this error, because of the extra ' ' the NOW() puts in the odbc date. So how to work around this?

mm

 

[r937]

may i suggest that you use the database server's date as the column value, rather than trying to pass in a date string from the scripting language

in ordinary circumstances, your database server will have the correct date, so there's really no reason to supply a date from a different source

INSERT INTO Comments
(LogDate, other columns)
VALUES
(getdate(), other values)

this is tons easier than wrestling with strings that you assemble outside the database

rudy

http://rudy.ca/

 

[NoWayIsThisInUse]

...or just do a CreateODBCDate(now())

 

[flowcontrol]

That's simple. Just use CreateODBCDateTime():


<CFQUERY DATASOURCE=&quot;#client.coDatabase#&quot;>
INSERT INTO Comments
(LogDate,TicketNumber,Type,UserID,Comments)
VALUES
(#CreateODBCDateTime(Now())#,
'#FORM.TICKETNUMBER#', '#FORM.TYPE#', '#FORM.USERID#','#FORM.COMMENTs#')
</CFQUERY>

 

[mmorgensen]

yep! My problem ended up being two fold. Wrong data type in SQL, and putting ' ' around the odbc date in CF.

working great now!

thanks all

mm