Inserting datetime in MSSQL from Coldfusion

Question

I am trying to insert NOW into a MySQL table. Something like:

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

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

But I am getting the following error:

Invalid JDBC timestamp escape

Any help?

Answer 1

Let ColdFusion write out the data for you - using cfqueryparam. It's not absolutely essential here, but it's good practice to use it whenever you can. In addition to protecting you from SQL injection, it formats your variables appropriately so you don't have to worry about whether or not you need to insert the values as strings or integers or whatever.

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

Answer 2

If you want the date, without the time, 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 will remove any time, and depending on your field type show either the date only or the date with 00:00:00 as the time value.

If you want a date with time:

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

Answer 3

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

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