[In preview] More flexible date formats in Polybase
This feedback item is currently in preview here:
Polybase currently has very limited date format support. All of the acceptable date formats have dashes or slashes. For example, I can't define a date format of yyyyMMdd since it doesn't have slashes or dashes. Please allow any date format I can express.
Also, please allow more than one date format per external table. For example, what if I have a date column as yyyyMMdd and then a different column as yyyyMMddHHmm. With only being able to define one date format in the CREATE EXTERNAL FILE FORMAT statement I think I can't handle both formats.
Jason Horner commented
This is causing us to use either orc or parquet as workaround but this limits the ability to read a single file in parallel.
Neil Palmer commented
It would be great if Polybase could implement support for U-SQL dates. It's a shame there isn't better support between the two technologies.
Due to datetime field conversion shifting an hour forward or backward due to being processed in different time zones, we export and import datetime fields as text and convert to datetime when we select from the External Table using SQL. If the conversion requires complicated logic (i.e., you can't use CONVERT), you could write a function.
Please add support for following format : yyyy-MM-ddTHH:mm:ss.fffffff
This is the default datetime format from U-SQL CSV Outputter.
Ethan DuBois commented
I wholeheartedly agree. This is a real issue for a product I'm working on, not being able to handle multiple date formats for a file. What if I have a date of birth column (DATE) and other DATETIME2 columns (metadata, etc)??
Is there any update on this?
Matthias Heinsius commented
Additionally provide support for having fields with Date and others with Datetime within the same file. This does not work if you have to specify your date_format and can't use the default.
Please add support for the following format: yyyy-MM-ddTHH:mm:ss.fffffffZ
This is the date format that Azure Stream Analytics outputs. (or advise on workaround that doesn't involve using varchar). Thanks
Kaiwalya Joshi commented
Additionally provide support for ISO8601 Combined Date and Time Representations
eg: "2007-04-05T14:30Z" or "2007-04-05T12:30-02:00"