使用ColdFusion在MySQL中插入日期和时间 [英] Insert date and time into MySQL with ColdFusion

问题描述

我完全迷失在这里。

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

  cfset myDateTime = CreateODBCDateTime（07-04-2012 20:11:00）> 
  

稍后：

 < cfquery name =qAddDate> 
 INSERT INTO some_table 
（`date`）
 VALUES 
（'＃myDateTime＃'）
< / cfquery>但是，当尝试向数据库发送数据时，我收到此错误：
 
 
 
 
 
您的SQL语法有错误;请检查与您的MySQL服务器版本对应的手册，以获取正确的语法在第8行附近使用'2012-07-04 20:11:00'}'）'
 
 
第8行是包含日期的行：
  INSERT INTO some_table 
 date`）
 VALUES 
（'{ts'2012-07-04 20:11:00'}'）
  
 $ 
解决方案     / div> 
您不需要在对象周围加引号，而只需要字符串。删除引号应该可以解决您的语法错误：
  INSERT INTO some_table（`date`）
 VALUES（＃myDateTime＃ ）
  
虽然你应该习惯使用 cfqueryparam  
  INSERT INTO some_table（`date`）
 VALUES（< cfqueryparam value =＃myDateTime＃ cfsqltype =cf_sql_timestamp>）
  
 ...如果它是有效/可分析的美国日期字符串，可以跳过createODBCDate，只需使用：
  INSERT INTO some_table（`date`）
 VALUES < cfqueryparam value =07-04-2012 20:11:00cfsqltype =cf_sql_timestamp>）
  
 
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.
解决方案
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"> )


                        
这篇关于使用ColdFusion在MySQL中插入日期和时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！