Troubleshooting MS SQL Server: ERROR [22007] Conversion failed

fe-c — Thu, 28 Sep 2023 10:00:44 GMT

Hello together. When I try to preview/import a view (table) from MS SQL server I get the message:

Microsoft SQL Server - Connector:

ERROR [22007] Conversion failed when converting date and/or time from character string.,

If I load the same view from Google BigQuery (import from MS SQL) or by using the ODBC connector instead of the Qlik Microsoft SQL Server connector it's working correctly.
When I load not the view but rather the source table from SQL server its also working without any errors.

I can't see any differences between the definitions of date/time fields in the table and the view. So which options do I have to investigate/understand this behavior?

In history loading from Microsoft SQL Server connector was quiet faster than through ODBC connector.

Re: Troubleshooting MS SQL Server: ERROR [22007] Conversion failed

fe-c — Thu, 07 Nov 2024 13:06:03 GMT

We have now found a solution.

The cause was a function that was integrated in the SQL table to be called. This contained a 'datediff()' call which could not be evaluated by the Qlik MS SQL connector.

DATEDIFF ( datepart , startdate , enddate )

The solution was to replace this function with a floating point calculation.

However, since the old function (incl. 'datediff()') worked with the Qlik ODBC connector (on premise), I guess there might be a bug in the Qlik MS SQL connector (on premise & saas).

topic Troubleshooting MS SQL Server: ERROR [22007] Conversion failed in Connectivity & Data Prep

Troubleshooting MS SQL Server: ERROR [22007] Conversion failed

Re: Troubleshooting MS SQL Server: ERROR [22007] Conversion failed