http://coldfusion.com logo
Join Slack
Powered by
Hey lucee folks, I have an mssql query issue. How ...
# lucee
r
Hey lucee folks, I have an mssql query issue. How is it possible for this one result to be returned from this query:
As you can see, the query explicitly request records with a 
transDate
that is less than 
{ts '2024-11-16 00:15:05'}
and the record returned has that exact date
g
What is the time on the server?
r
it's UTC, but why would that have anything to do with it?
g
it doesn't, I was thinking of something else, sorry
r
when I run the exact same query in mssql studio, I get zero results
when I change it to 
2024-11-16 00:15:06
in mssql, I get 2 records that I would expect to get:
a
That's not the same query though, is it?
r
Yes, that's the exact same query
unless I'm missing something
a
One's a timestamp and one's a string being coerced into a timestamp.
g
are you using a data type with the cfqueryparam?
a
If there's timestamp translations or shenanigans happening...
r
I don't think so
image.png
mssql studio is giving me the correct results. In my lucee code, I'm setting transDate with a query param value, which is a variable that is equal to 
{ts '2024-11-16 00:15:05'}
(as a string). The resulting query can be seen in that initial cfdump that I posted
g
can you specify a data type and see if there is a difference?
r
to make sure that the date is correct, I added a second to it and now I'm getting the same results as when I add a second to it in mssql studio
image.png
what do you mean by "specify a data type"
g
1 second latency in the connection? j/k
r
haha
When I query the date inside the select query instead of using the Lucee variable, I get the correct result:
g
I suspect the metric system
in general
b
I would assume the actual date has ms in it and is technically a few ms greater
CF tends to strip ms out of dates from what I've seen
so even though it shows in your query result as just 1:15 and 5 seconds, in the actual DB does it have a ms value?
a
That's why I was griping about the string/timestamp comparison. Searching in Lucee with the string timestamp, not the param but just the quoted string you used in MS Studio, might work correctly.
g
it does have MS in it. He posted a screenshot above.
r
I tried both ways
I think it's a bug, but I'll need to do a bunch work to verify, which I don't have time to do. Instead, I'll modify my query, make a mental note, and find the same bug in about 3 years and not remember any of this
g
to Brad's point, it may be defaulting the ms to 000 and since the value is greater than that, it is technically correct in not returning it
b
ok, so this is working as designed, no?
If the time is 5:15 pm and I say get me the dates after 5:00, then 5:15 would be returned
Even though they're both the "5" o'clock hour, one is still later than the other due to an additional fraction of an hour
Now just read that again, but swap milliseconds for minutes
it's the same concept
00:05:15:443
is later than 
00:05:15:000
There is also likely some rounding happening, but only in one case
If you pull a quick trace on the SQL server, you can see the exact SQL being run and what types are in use
I'm not clear if your code is using cfqueryparam or not
e
second that suggestion ⬆️ run a trace on SQL server and see what the actual query being run is
r
thanks all... as an FYI, these records are being stored with a fraction of a second included in the timestamp. This is done intentionally because some transactions are created within the same second, and they need to be stored chronologically by date. The timestamp for the record with 
transID=7759
is stored in the database as 
2024-11-16 00:15:05.427
. However, that doesn't explain how this record is still being returned since the select statement is looking for records that are less than 
2024-11-16 00:15:05.000
(assuming the seconds are being truncated in the timestamp).
m
i try not to assume what they are doing with the ms, as it very well could be applying .999 instead of your .000 expectation. when the ms may cause issues, i will cast() the field on the left side of the operator, and bind the param on the right side to try to get comparisons that are more apples to apples.
r
what datatype of the MS SQL column ?
I just happened to learn that MS SQL can store 100ns precision 😯
so. tried at home 🙂 per documentation, "datetime" stored milliseconds, but precision is 3.33ms
I created some records with milliseconds, and one of them was "2024-12-21 135032.000" Then tried to select WHERE moment < "2024-12-21 135032.000". As expected, record did not returned. Surprise that select WHERE moment < "2024-12-21 135032.001" did not yielded the record either! Only when I asked moment < "2024-12-21 135032.002" the record returned. So MS SQL struggle to select something within 3ms range, and may not work as expected
Interesting that trying to create record with .012 millisecond, MS SQL created record with .013 millisecond - rounding error
e
@rodyon just out of curiosity, try using DateDiff function instead of comparison < operator and see if that makes a difference
r
dateDiff in milliseconds ("l") on Lucee6 side looks OK (datediff-ed by query loop): 2024-12-21 135032.000 diff 2024-12-21 135420.000: -228000 2024-12-21 135032.000 diff 2024-12-21 135032.000: 0 2024-12-21 135032.000 diff 2024-12-21 135031.753: 247 2024-12-21 135032.000 diff 2024-12-21 135032.013: -13
12 Views
Open in Slack
PreviousNext
Powered by