Geek asks and answers

Insert date and time in MSSQL from Coldfusion

I am trying to insert NOWinto a MySQL table. Sort of:

<cfset datatime = CREATEODBCDATETIME( Now() ) />

<cfquery name="qInsert" datasource="#dbanme#" >
   INSERT INTO TableName(....,date_created, date_modified)
   VALUES(...,'#datatime#', '#datatime#')
</cfquery>

But I get the following error:

Invalid JDBC Timestamp Output

Any help?

+5
source share
4 answers

Let ColdFusion output the data for you - using cfqueryparam. It is not entirely important here, but it is good practice to use it whenever you can. In addition to protecting against SQL injection, it formats your variables accordingly, so you don’t have to worry about whether to insert values ​​as strings or integers or something else.

<cfset datatime = CREATEODBCDATETIME( Now() ) />

<cfquery name="qInsert" datasource="#dbanme#" >
   INSERT INTO TableName(....,date_created, date_modified)
   VALUES(...,
        <cfqueryparam value="#datatime#" cfsqltype="cf_sql_timestamp">,
        <cfqueryparam value="#datatime#" cfsqltype="cf_sql_timestamp">
    )
</cfquery>
+10
source

If you want the date, without time, to use the following:

<cfquery name="qInsert" datasource="#dbanme#" >
   INSERT INTO TableName( ...., date_created, date_modified )
   VALUES ( ...
        , <cfqueryparam cfsqltype="cf_sql_date" value="#now()#">
        , <cfqueryparam cfsqltype="cf_sql_date" value="#now()#">
   )
</cfquery>

cf_sql_date , 00:00:00 .

:

<cfquery name="qInsert" datasource="#dbanme#" >
   INSERT INTO TableName ( ....,date_created, date_modified )
   VALUES ( ...
      , <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
      , <cfqueryparam cfsqltype="cf_sql_timestamp" value="#now()#">
   )
</cfquery>
+7

If you want to use the built-in NOW function, just enable it as part of the request:

<cfquery name="qInsert" datasource="#dbname#" >
   INSERT INTO TableName(....,date_created, date_modified)
   VALUES(...,NOW(), NOW())
</cfquery>
+3
source

You do not need quotes around the generated ColdFusion timestamps when pasting into SQL Server.

To develop, if you create your timestamp as a string using DateFormat and something else, you should paste it into this format:

INSERT INTO TABLE(DATE_COL) VALUES({ts '#dateInAStringFormat#'})

ColdFusion ODBCx functions for dates do all this for you, so it's simple:

INSERT INTO TABLE(DATE_COL) VALUES( #dateInAStringFormat# )
0
source

More articles:


All Articles