Insert date and time into MySQL with ColdFusion

Question

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.

Answer 1

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"> )