1

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

Hannah Vernon
  • 66,859
  • 22
  • 158
  • 297
GHauan
  • 553
  • 4
  • 19
  • Maybe this helps: [PolyBase export of datetime values does not use DATE_FORMAT](https://techcommunity.microsoft.com/t5/azure-database-support-blog/polybase-export-of-datetime-values-does-not-use-date-format/ba-p/368932) And I dont know if you can set date format when create external table: https://stackoverflow.com/questions/67708065/polybase-error-converting-data-type-varchar-to-datetime – McNets Oct 08 '21 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 08 '21 at 13:36

0 Answers0