Insert datetime with milliseconds into an MSSQL database using ColdFusion

Question

Using ColdFusion (Lucee 4.5), I need to insert datetime values ​​that include milliseconds into my MSSQL database. I am creating a UTC datetime value as follows:

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 does not include milliseconds. The values ​​inserted in db are as follows:

2015-10-26 02:14:07.000

The last 3 digits after .(period) at the end is the proportion of the MSSQL of the second record (1/300), which is always.000

How to enable milliseconds or a split second? 1/300fine.

Answer 1

Lucee , , cfqueryparam. CF11. , , cfqueryparam timestamp, createODBCDateTime:

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

Update:

Redtopia, , cfscript addParam():

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

Answer 2

sql , , , MS SQL . . GETDATE (Transact-SQL) GETUTCDATE (Transact-SQL)

:

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

:

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