Sample Header Ad - 728x90

Polybase converts getdate() to the wrong format

1 vote
0 answers
280 views
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 13.32.41.373' 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).
Asked by GHauan (615 rep)
Oct 8, 2021, 01:00 PM
Last activity: Oct 8, 2021, 08:17 PM