CODEWITHSUNDEEP
sql-serverdatetimecoldfusionodbclucee
Redtopia
Redtopia

Reputation: 5247

Insert datetime with milliseconds into MSSQL database using ColdFusion

Using ColdFusion (Lucee 4.5), I need to insert datetime values that include milliseconds into my MSSQL database. I'm creating a UTC datetime value like this:

nowUTC = dateConvert("Local2UTC", now());
nowODBC = createODBCDateTime(nowUTC);

then I use the following SQL code to insert:

insert into tbl (theTime) values (#nowODBC#)

However, this method doesn't include milliseconds. The values inserted into the db look like this:

2015-10-26 02:14:07.000

The last 3 digits after the . (period) at the end is MSSQL's fraction of a second notation (1/300), which is always .000

How can I include milliseconds or a fraction of a second? 1/300 is fine.

Upvotes: 4

Views: 2095

Answers (2)

Leigh
Leigh

Reputation: 28873

I cannot test with Lucee at the moment, but I suspect the problem is not using cfqueryparam. The results are the same under CF11. To insert the date and time, including milliseconds, use cfqueryparam with type timestamp, not createODBCDateTime:

<cfquery ....>
   INSERT INTO tbl (theTime) 
   VALUES 
   ( 
     <cfqueryparam value="#nowUTC#" cfsqltype="cf_sql_timestamp">
   )
</cfquery>

Update:

As Redtopia mentioned in the comments, the cfscript version would be addParam(): 

query.addParam(name="theTime"
                , value=nowUTC
                , cfsqltype="cf_sql_timestamp"
              );

Upvotes: 5

David Sirr
David Sirr

Reputation: 41

Try directing the query to use one of the native sql server functions for current timestamp including millisecond, this will vary depending on which version of MS SQL you are running. See GETDATE (Transact-SQL) and GETUTCDATE (Transact-SQL)

You may find something like this is what you want:

insert into tbl (theTime) values (GETUTCDATE())

example: 

GETUTCDATE() = 2015-10-27 20:10:02.047

Upvotes: 4

Related Questions