CODEWITHSUNDEEP
sqlcoldfusion
user160820
user160820

Reputation: 15200

Inserting datetime in MSSQL from Coldfusion

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?

Upvotes: 5

Views: 17159

Answers (4)

Kyle Hale
Kyle Hale

Reputation: 8120

You don't need quotes around the generated ColdFusion timestamps when inserting into SQL Server.

To elaborate, if you build up your timestamp as a string, using DateFormat and whatnot, you have to insert into using this format:

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

The ColdFusion ODBCx functions for dates do all this work for you, so it's just:

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

Upvotes: 0

Lance
Lance

Reputation: 3213

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>

Upvotes: 4

Joe C
Joe C

Reputation: 3546

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>

Upvotes: 13

steve
steve

Reputation: 1490

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>

Upvotes: 7

Related Questions