Skip to main content
B
bw_bloodletter
Inspiring
March 2, 2020
Answered

CF 2016 CFSQLTYPE CF_SQL_TIMESTAMP Failing After Leap Day (2/29)?

  • March 2, 2020
  • 1 reply
  • 1445 views

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.

 

Any ideas?

This topic has been closed for replies.
Correct answer Charlie Arehart

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:

DateTimeInputParameterType=dateTime

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:

 

https://knowledgebase.progress.com/articles/Article/8130

 

It's even mentioned within the CF docs here:

 

https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html

 

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.

1 reply

Charlie Arehart
Community Expert
Charlie ArehartCommunity ExpertCorrect answer
Community Expert
March 2, 2020

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:

DateTimeInputParameterType=dateTime

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:

 

https://knowledgebase.progress.com/articles/Article/8130

 

It's even mentioned within the CF docs here:

 

https://helpx.adobe.com/coldfusion/configuring-administering/data-source-management-for-coldfusion.html

 

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.

/Charlie (troubleshooter, carehart. org)
    B
    bw_bloodletterAuthor
    Inspiring
    March 2, 2020

    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.

     

    Charlie Arehart
    Community Expert
    Charlie ArehartCommunity Expert
    Community Expert
    March 2, 2020

    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.

    /Charlie (troubleshooter, carehart. org)
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices