How can we improve Microsoft Azure SQL Data Warehouse ?

More flexible date formats in Polybase

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.

60 votes
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Greg Galloway shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

    9 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...
      • Jason Horner commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Anonymous commented  ·   ·  Flag as inappropriate

        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.

      • Jaime commented  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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  ·   ·  Flag as inappropriate

        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.

      • Shane commented  ·   ·  Flag as inappropriate

        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

      Feedback and Knowledge Base