We have many queries which do something similar to:
WHERE dateCol > <CFQUERYPARAM VALUE="#someDateString#" CFSQLTYPE="CF_SQL_TIMESTAMP">
Today, users informed us that existing reports were returning unexpected results.
We use datetime2(7) columns in an MS SQL server.
As an example, we have data (3 rows) in a table with dateCol values of "2020-03-01 10:00:00.54321".
We run a query with a clause of:
WHERE dateCol > <CFQUERYPARAM VALUE="2020-03-01 10:00:00.54321" CFSQLTYPE="CF_SQL_TIMESTAMP">
These queries have been in place for years, and up until today they were working fine. The example query was returning 0 rows (as the timestamps were equal).
Today, the query is returning the 3 rows as if the converted value (from whatever CFSQLTYPE does internally) is earlier than 2020-03-01 10:00:00.54321.
Removing CFSQLTYPE="CF_SQL_TIMESTAMP" makes it work as expected. Running the query in SSMS without CFQUERYPARAMVALUE at all makes it work as expected. Adjusting the values on the row data or the string in the query by as little as .00001 in either direction works as expected, on a variety of data, so I don't think there's any issue with precision/rounding in implicit conversions.
Copy link to clipboard
I don't see how this would be affected by the leap year, nor why otherwise it would "just start" to affect you, but I have seen problems with such datetime fields, when the values in the DB had millisecond values like you are showing, and where (as you found) removing the cfqueryparam made things "work".
In the case of the client I helped with this, we found one solution that suited them. It may or may not suit you. They were using the built-in sql server driver that comes with CF. Are you? (One can also download and use a MS-provided driver, which would have its own possible solution.)
In their case, we found that if we added a "connection string" value (in the CF Admin, in the "Advanced settings" for the DSN:
Immediately, without a need of a CF or SQL Server restart, the problem went away. If you may want to read more about this conn string, see the docs for the db driver with Adobe licenses from Progress (formerly DataDirect, formerly Merant), here:
It's even mentioned within the CF docs here:
And while both of those refer to SQL Server 2008, I am pretty sure my client was using a much more recent SQL Server version. How about you?
Let us know how it goes.
Thnks for the reply.
We're using the default, built-in driver for the connection, and we're using SQL Server 2016.
In my brief testing, I found that wrapping stuff in a DATEADD() and adding 100 microseconds (mcs) would result in some rows dropping off of the result set. Adding 200, 300, etc. resulted in more rows being dropped of. By the time I added a full millisecond, all extraneous rows had been removed, and the result set was as expected.
So it does appear to be some sort of precision/rounding issue, but I still have no idea what caused it to appear today (or over the weekend). These are all stored values being compared (a dateime2(7) from one table against a datetime2(7) in another table). The documentation for CF_SQL_TIMESTAMP indicates it converts to datetime, but I don't know if that's accurate or not. These queries have been used with datetime(2) values for years with no issues due to the lack of precision datetime has compared to datetime(2).
I'll try out the DateTimeInputParameterType=dateTime connection string parameter if this pops up again. I believe I've already handled all the instances where it could result in different results being returned.
OK, but I'll add one more tip, in case you have some reluctance to try this connectionstring: you can certainly setup another DSN, with a different name but all the other values identical, and only in that NEW DSN put in this connection string. Then you can test that in some sample code, where the only thing you're changing is the DSN name in the cfquery.
If you get to try out the connectionstring value, one way or another, do let us know, so future readers know if this could help them also.
I noticed that I never followed up on this, even though I marked your suggestion as the correct answer.
I believe we adjusted all of our queries to not be affected by this (by removing the CFSQLTYPE parameter for those columns), but we also made the suggested change to the connection string, just to prevent us from running into this in the future.
Glad to hear all is resolved. Thanks for the update.