使用 ColdFusion 将日期和时间插入 MySQL

2022-01-09 00:00:00 datetime mysql coldfusion

我完全迷路了.

MySQL 数据库中有一个日期时间"类型的字段.我想用 ColdFusion 程序生成的日期时间填充它.我发现必须使用 CreateODBCDateTime 转换为属性格式，以便 MySQL 接受它，所以...

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

然后在某个地方:

<cfquery name="qAddDate">
    INSERT INTO some_table
    (`date`)
    VALUES
    ('#myDateTime#')
</cfquery>

但是，当我尝试将数据发送到数据库时出现此错误:

However, I get this error when try to send data to database:

您的 SQL 语法有错误；检查与您的 MySQL 服务器版本相对应的手册，以在第 8 行的 '2012-07-04 20:11:00'}')' 附近使用正确的语法

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

第 8 行是带日期的行:

Line 8 is the line with date:

INSERT INTO some_table
(`date`)
VALUES
('{ts '2012-07-04 20:11:00'}')

有人可以帮忙吗?

谢谢.

推荐答案

日期对象不需要引号，只需要字符串.删除引号应该可以解决您的语法错误:

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

虽然你应该养成使用 cfqueryparam

  INSERT INTO some_table (`date`)
  VALUES ( <cfqueryparam value="#myDateTime#" cfsqltype="cf_sql_timestamp"> )

...或者，如果它是有效/可解析的美国日期字符串，您可以跳过 createODBCDate 并使用:

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

相关文章