Highlighted

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

Explorer ,
Mar 02, 2020

Copy link to clipboard

Copied

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?

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

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.

TOPICS
Database access, Reporting, Server administration

Views

609

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

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

Explorer ,
Mar 02, 2020

Copy link to clipboard

Copied

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?

Adobe Community Professional
Correct answer by Charlie Arehart | Adobe Community Professional

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.

TOPICS
Database access, Reporting, Server administration

Views

610

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Mar 02, 2020 0
Adobe Community Professional ,
Mar 02, 2020

Copy link to clipboard

Copied

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.h...

 

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 (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 02, 2020 1
Explorer ,
Mar 02, 2020

Copy link to clipboard

Copied

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.

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 02, 2020 0
Adobe Community Professional ,
Mar 02, 2020

Copy link to clipboard

Copied

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 (server troubleshooter, carehart.org)

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Mar 02, 2020 0