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?

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

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

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

Comments

[Leigh]

Did you mean cf_sql_timestamp? Because using cf_sql_date will remove any time portion and insert a date only.

[Joe C]

oops, yeah - habit out of our own practices where we name things either date_xx or datetime_xx appropriately

[Leigh]

Cool, I figured it was just a typo of sorts :) Thanks for updating it.