querying SQL Server from Apache Drill and datetimeoffset

2
votes

I am trying to query SQL Server from Apache Drill but I get some issues when the SQL Server tables contain datetimeoffset (SQL Server type) columns.

Any SELECT query from Drill to SQL Server on this kind of table lead to the response:

Error:

VALIDATION ERROR: Unknown SQL type -155

I am certain it comes from the datetimeoffset column in the table, since I tested the same kind of queries on tables with no datetimeoffset columns and obtained satisfying results.

I went through the documentation of Drill (e.g. this page https://drill.apache.org/docs/supported-data-types/) and tried to cast the datetimeoffset column to SQL supported types but nothing worked and Apache Drill kept answering me the same error.

Do you have any idea how to get through this please?

1
sql-serverdatetimeoffsetapache-drill

1 Answers

0
votes

I can confirming that it is in fact a problem with DATETIMEOFFSET https://msdn.microsoft.com/en-us/library/bb677267.aspx not being well handled by the JDBC library.

Only resolution that I currently know of is to convert the data on the way out like so:

,CAST(my_datetimeoffset_col AS DATETIME2)                  as datetime_local           
,CAST(SWITCHOFFSET(my_datetimeoffset_col, 0) AS DATETIME2) as datetime_utc             
,DATEPART(TZ, my_datetimeoffset_col)                       as datetime_tz

You could choose to only output UTC or local time but in my experience DATETIMEOFFSET was chosen because both were going to be needed.