We have two SQL Servers (both running Enterprise version on 15.0.4083.2) where we use queries to External tables via Polybase from one server to the other.
We have encountered a strange error when we use the
getdate() function to filter against a
datetime column. The query fails with the following error message:
Msg 7320, Level 16, State 110, Line 4
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)".
105082;Generic ODBC error:
[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. .
The query used from the server where the external table is created is as follows:
SELECT TidID FROM Felles.DimTid WHERE TidID <= GETDATE();
The query converted by Polybase (I think) and executed at the destination server (where it fails) is as follows (from SQL Profiler):
SELECT [T1_1].[TidID] AS [TidID] FROM ( SELECT [T2_1].[TidID] AS [TidID] FROM [DBName].[Felles].[DimTid] AS T2_1 WHERE ([T2_1].[TidID] <= CAST('2021-10-08 126.96.36.1993' AS datetime)) ) AS T1_1;
The problem is how it has converted the
GetDate() function: it uses periods between hour.minute.second, instead of using colons (hour:minute:second).
Is there a way for me to change this behavior in our system settings? (We do not want to have to manipulate the queries themselves).