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:

FROM Felles.DimTid

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]
    SELECT [T2_1].[TidID] AS [TidID]
    FROM [DBName].[Felles].[DimTid] AS T2_1
    WHERE ([T2_1].[TidID] <= CAST('2021-10-08' 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).

  • Maybe this helps: PolyBase export of datetime values does not use DATE_FORMAT And I dont know if you can set date format when create external table: stackoverflow.com/questions/67708065/…
    – McNets
    Oct 8 at 13:12
  • @McNets Thank you for your suggestions. I had seen these articles, but I can't see that they apply to our case where the queries are done from SQL Server table to SQL Server table. I cannot set the dateformat in the creation of the external data source. The weird thing for me is how the GetDate() is converted to a string and back again, but the format is unknown for SQL Server.
    – GHauan
    Oct 8 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.