Kaiwalya Joshi Kaiwalya Joshi - 1 year ago 102
Scala Question

Azure SQL Datawarehouse - JVM SimpleDateFormat support

I'm currently hitting a limitation on Azure SQL DW when converting timestamps from the JVM SimpleDateFormat domain
to SQL DW's DateFormat domain.

In my current scenario, we're using Spark to perform data cleanup, when data is written out as a CSV file, its written out as a timestamp in the following format:

  • "yyyy-MM-dd'T'HH:mm:ss.SSSXXX"

  • 2001-07-04T12:08:56.235-07:00

Looking at DateTimeOffset under the DATE_FORMAT options, the bolded T in the above output isn't supported.

Is there any way to directly interpret the incoming string as a DateTime type?

My current strategy is to load it as a VARCHAR and do cleanups later.

Answer Source

It looks like unless you transform the datetime itself before it hits SQL DW, it won't be interpreted as datetime due to the T. Your best bet is having it as varchar and then casting it as a datetime in SQL DW as listed here: https://msdn.microsoft.com/en-us/library/ms187928.aspx#Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

I would recommend also casting a vote in the feature request portal for more flexible datetime formats so you don't have to go through this process

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download