Skip to main content
Announcements
April 9th: The AI Roadmap: 6 Landmarks for AI-ready Data and Analytics: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
fe-c
Contributor III

Troubleshooting MS SQL Server: ERROR [22007] Conversion failed

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.

 

1 Solution

Accepted Solutions
fe-c
Contributor III
Author

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

View solution in original post

1 Reply
fe-c
Contributor III
Author

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