Converting Issue Datetime MS SQL vs. Qlik Sense Enterprise

[roberttemplin]

Hey everyone,

currently I'm dealing with some issues loading data from a MS SQL database into my qlik sense app. 

1. I've set up an MS SQL Server connection within my qlik sense hub. ==> ok

2. I load some tables (ABC, DEF,...) , containing different data types (bool, varchar, datetime,...) ==> ok

3. I try to load a VIEW, which reduced the amount of records of table ABC by "where  tab_date >= '2023-12-31' just to get records für 2024 and 2025 ==> ERROR 

3.1. I tried different ways (geman format '31.12.2023' etc.) with no success. Everytime I get following error:

"ERROR [22008] The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.,"

4. Ok, so I tried to filter by '2023-31-12' or '12.31.2023' and this works fine. But this can't be the solution, because qlik sense now gets the records, but in MS SQL Management Studio my result is empty.

I know, there are alternatives like year(tab_date) > 2023, but this is just an example and there are 100 of Views in the DWH and it is not practical to edit all Views, containing such terms.

Seems like, it is an ODBC issue, isn't it? I set up an custom ODBC connection with an system dsn. ==> It works, although both, ODBC and SQL Server connector use ODBC to connect.

I set up an OLE DB Connection...it works as well.

Any ideas how to fix it? My Database language is "english US", qlik sense was set up in german. Is this an unsupported setup? Can't believe it!

Some information about qlik Sense:

Qlik Sense May 2024 Patch 11

qliksenseserver: 14.187.17

Qlik ODBC Connector 7.61.0

SQL Server Enterprise

Windows Server 2019 Standard

Version: 15.0.4410.1

[marcus_sommer]

I think your attempt of: year(tab_date) > 2023 goes already in a suitable direction. Because it means that no view needs to be touched else the call could be appropriate adjusted - and this may not mandatory a date-function else it might be any conversion, like to_varchar, cast, to_int (depends of what the data-base and the driver supports).

Ideally it's always a pure number - from the source over any transformations into the target - to avoid most of the data-type stuff ... 

[roberttemplin]

@marcus_sommer : Thx. for your reply.

In that case, it would be a possible way to fix it, but...

1. what about dynamic filter like

tab_date <= '2025-06-20' (CURRENT DATE) there is no way to replace it by a function like year(), month(), datepart() etc. because the filter must be dynamic. 

2. this will solve the problem, but not the cause

I can't belive, that Qlik nor MS or other software producers did not deal with topics like different system languages/ data type formats. There must be a solution in reconfiguring Qlik Sense or windows to fix that issue. I HOPE SO 😄 

[marcus_sommer]

Of course you could use dynamic values, for example like:

let v = date(today(), 'YYYY-MM-DD');

sql select * from X where tab_date = '$(v)';

[roberttemplin]

Sure, but this way I have to edit all relevant views in the DWH to fix it. Each DWH view is like a little DataMart, which can be used easy by the bi developers. Would prefer not to change them, cause there are "just" format convertion issues.

And as I can see, ODBC Connection and OLE DB Connection can handle the data and the queries. So it is not understandable  why the Microsoft SQL Connection can't handle it

[marcus_sommer · Accepted solution]

I suggest to look within the MS data-base community to get hints to special requirements to the syntax and to any possible dependencies of configurations within the data-base and/or the driver.

I wouldn't call it "just format convertion issues". Within the Qlik community has this stuff a huge amount - and I think it's the same by nearly all other (programming) tools. Of course, there is always a technically possibility to address these data but it will increase the complexity quite significantly if n different methods are needed. In Qlik it depends if a (field/variable) value is a string, a (pure) number or a formatted content to where and how it's created and later called. The development, maintaining and documentation efforts could become quite expensive.

It doesn't mean that there are no commonly formatted values within any views else only to perform if possible all the essential calculations and matching against pure numbers.