Insert datetime with milliseconds into MSSQL database using ColdFusion

Question

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.

Answer 1

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"
              );