I am storing datetime fields with milliseconds in a MSSQL database, which is especially important for financial transactions, but is also important for user generated content because multiple posts can be saved within a one second period of time.
I have no issues getting dates with milliseconds from the database, but I do have issues when I try to run select queries using dates when they include milliseconds because Lucee drops milliseconds when using cf_sql_timestamp in query parameters.
I know this because when I look at a query generated from queryExecute() that includes a date field with milliseconds, the milliseconds is dropped from the date.
I can also see this happening when I do something like this:
isoDate = "2025-04-10T01:08:44.413Z";
date = LSParseDateTime(isoDate);
dump(var=date, label="date", abort=true);
The output is:
Is there a way to get around this?
what does DateTimeFormat(date, "yyyy-mm-dd'T'HH:nn:ss:lll XXX") show?
the lll being milliseconds in the mask?
internally, all dates are milliiseconds since 1970
<cfoutput>
<cfquery name="selectQuery" datasource="LDEV2708">
SELECT GetDate() as myDate
</cfquery>
<cfscript>
systemOutput(selectQuery.myDate, true);
systemOutput(DateTimeFormat(selectQuery.myDate, "yyyy-mm-dd'T'HH:nn:ss:lll XXX"), true);
</cfscript>
<cftry>
<cfquery name="insertQuery" datasource="LDEV2708" result="result">
INSERT INTO LDEV2708(
when_created
)
SELECT <cfqueryparam value="#selectQuery.myDate#" cfsqltype="cf_sql_timestamp"/> AS when_created
</cfquery>
<cfquery name="q" datasource="LDEV2708" result="result">
select when_created from LDEV2708
</cfquery>
<cfscript>
systemOutput(q.when_created, true);
systemOutput(DateTimeFormat(q.when_created, "yyyy-mm-dd'T'HH:nn:ss:lll XXX"), true);
</cfscript>
#result.recordcount#
<cfcatch type="any">
#cfcatch.stacktrace#
</cfcatch>
</cftry>
</cfoutput>
outputs
[java] [script] test.tickets.LDEV2708
[java] [script] {ts '2025-04-10 14:13:45'}
[java] [script] 2025-04-10T14:13:45:810 +02:00
[java] [script] {ts '2025-04-10 14:13:45'}
[java] [script] 2025-04-10T14:13:45:810 +02:00
[java] [script] (1 tests passed in 1,655 ms)
so you can see the date being round tripped in and out of the database including the milliseconds (810)
The issue is when you use a date with milliseconds in a SELECT query that compares dates.
Try:
<cfquery name="q" datasource="LDEV2708" result="result">
select count(*) as ct
from LDEV2708
where when_created > <cfqueryparam value="#selectQuery.myDate#" cfsqltype="cf_sql_timestamp"/>
</cfquery>
In my Lucee world, and all Lucee worlds I’ve ever used, this query will include the record that you just added because the milliseconds are dropped in the cfqueryparam.
I’m not actually running this code so I can’t confirm… all my queries use queryExecute() with:
params=[{name="utcDate", value=utcDate, cfsqltype="cf_sql_timestamp"}]
[java] [script] test.tickets.LDEV2708 {ts '2025-04-10 15:12:04'}
[java] [script] 2025-04-10T15:12:04:867 +02:00
[java] [script] {ts '2025-04-10 15:12:04'}
[java] [script] 2025-04-10T15:12:04:867 +02:00
[java] [script] query("ct":[0])
Here’s my test code that incorrectly returns records when comparing dates. This fails most of the time since it depends on what the date/time is when added to the database.
utcNow = DateConvert("Local2UTC", Now());
echo("<p>UTC Now: #DateTimeFormat(utcNow, "yyyy-mm-dd'T'HH:nn:ss:lll XXX")#</p>");
dump(var=utcNow);
queryExecute(sql="
INSERT INTO TestDates (
crDate
) VALUES (
:utcNow
)
",
params=[
{name="utcNow", value=utcNow, cfsqltype="cf_sql_timestamp"}
],
options={
datasource: dsn
}
);
results = queryExecute(sql="
SELECT *
FROM TestDates
",
options={
datasource: dsn
}
);
dump(var=results);
for (result in results) {
echo("<p>crDate: #DateTimeFormat(result.crDate, "yyyy-mm-dd'T'HH:nn:ss:lll XXX")#</p>");
}
results = queryExecute(sql="
SELECT *
FROM TestDates
WHERE crDate > :utcDate
",
params=[
{name="utcDate", value=utcNow, cfsqltype="cf_sql_timestamp"}
],
options={
datasource: dsn
}
);
dump(var=results, abort=true);
Results:
I think your problem is that sql server is only accurate down to 1/300 of a second
That certainly could be the underlying issue. When I run the test over and over, it fails about 1/2 of the time. However, when I add the date to the params as an ISO 8601 string that includes millisecods, it works 100% of the time.
{name="utcDate", value=isoDate, cfsqltype="cf_sql_varchar"}
But this is weird:
In this test, MSSQL should return the record.
It looks like the date isn’t coming back from the server accurately - ms are off by 2.
SOLVED:
I changed the datetime field in MSSQL to datetime2(3) which is accurate to milliseconds.
Thanks @Zackster for the links. I knew that datetime fields in MMSQL were only accurate to 3ms, but I didn’t think I was running into that issue until I made my test script and then read more about the issue on the MSSQL side of things. Totally makes sense now.
2 Likes
Glad we solved it, I wasn’t aware of that issue myself, but my spidey senses were triggered there might be something like that.
Hopefully the next person battling this problem finds this thread!
1 Like
