Qlik Sense no longer respecting DateFormat and TimestampFormat variables

[kwdaniels]

On June 13 we upgraded from June 2018 to April 2019. Everything looked good when we compared our sheets between dev and production environments before reloading our data.  However, after we reloaded the data in production today for our weekly operational reporting, we noticed the date formats in the load script variables were no longer respected.

Our load script contains the following variables:

SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

The [Date] field is a SQL server datetime column. The [Date Name US] field is a string in the format MM/dd/yyyy.

This first screenshot shows the proper date display that respects the formats defined in the above variables. Note that the dates are sorted properly as dates, not strings, and note that the date/time field is formatted as expected:

Correct: Before reloading data

This next screenshot shows the dates improperly formatted after the data reload. Note that the [Date Name US] is sorted as a string, not a date. note also that the datetime field does not respect the format in TimestampFormat:

Bad: After data reload

Note that we transitioned from OLEDB (TLS 1.0) to a Microsoft SQL Server connection (TLS 1.2) on June 5, but we did not observe the issue above until after the upgrade to the April 2019 release of Qlik Sense.

This is not just a cosmetic issue; it is causing critical problems with our reports, many of which employ set analysis that requires date/time values to be formatted in a particular way. In other words, this is breaking our set analysis date filtering for many reports.

I'm attaching a qvf of an app demonstrating the issue. I'm also attaching SQL scripts (DimDateTest.sql) that create the table/view and populate the table needed for the app.

Any help would be greatly appreciated!

[kwdaniels · Accepted solution]

It turns out this issue was related to the use of the "Microsoft SQL Server" data source. When we use the ODBC driver, the dates are formatted as expected.

Here is the sequence of events:

1) We had been using the OLE DB connector successfully since we started using Qlik Sense in 2016

2) In early June of 2019, we transitioned from TLS 1.0 to TLS 1.3 on our Qlik server, which made so the OLE DB connector no longer worked. We found a recommendation on the Web to use the "Microsoft SQL Server" connector, so we started using it. We did not notice any issues.

3) A week later we upgraded from the June 2018 to the April 2019 release of Qlik Sense. After that, we started noticing issues with the date formats and our set analysis filters. We do not know whether this was an issue before the upgrade, but we did not notice it until after the upgrade.

4) We did some more research and decided to try the ODBC connector. This worked correctly for us, restoring our reports to full functionality.

Conclusion: the "Microsoft SQL Server" connector treats dates differently from the OLE DB and ODBC connectors. I recommend using ODBC if you have been using OLE DB and you need to upgrade to a newer version of TLS.