Insert date and time into MySQL with ColdFusion
coldfusion, datetime, mysql
Solution
You do not need quotes around date objects, only strings. Removing the quotes should resolve your syntax error:
INSERT INTO some_table (`date`)
VALUES ( #myDateTime# )
Though you should get into the habit of using `cfqueryparam`
INSERT INTO some_table (`date`)
VALUES ( <cfqueryparam value="#myDateTime#" cfsqltype="cf_sql_timestamp"> )
... OR if it is a valid/parseable US date string, you could skip the createODBCDate and just use:
INSERT INTO some_table (`date`)
VALUES ( <cfqueryparam value="07-04-2012 20:11:00" cfsqltype="cf_sql_timestamp"> )
Problem
I am totally lost here. There is a field of type "datetime" in MySQL database. I want to populate it with a datetime generated by ColdFusion program. I found that CreateODBCDateTime has to be used to convert to propert format so that MySQL would accept it, so... ``` <cfset myDateTime = CreateODBCDateTime("07-04-2012 20:11:00")> ``` And somewhere later: ``` <cfquery name="qAddDate"> INSERT INTO some_table (`date`) VALUES ('#myDateTime#') </cfquery> ``` However, I get this error when try to send data to database: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '2012-07-04 20:11:00'}')' at line 8 Line 8 is the line with date: ``` INSERT INTO some_table (`date`) VALUES ('{ts '2012-07-04 20:11:00'}') ``` Could anyone help? Thanks.