locked
ADW External file format for sample date "12/09/2019 04:00AM" having issues RRS feed

  • Question

  • Basically the title:

    I have a datetime column in a text file stored in Azure Data Lake that I am trying to import into my Azure Data Warehouse and I cant figure out the proper formatting for the datetime field when creating the external file format.

    The datetime looks like this:

    12/09/2019 04:00AM

    I have looked at the link here: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15

    which indicates it applies to ADW / Synapse Analytics

    It seems like a simple enough format but nothing I have tried in any of the tables on that link works.

    If I try something like:  

    DATE_FORMAT = N'MM/dd/yyyy hh:mmtt'

    I get an error, but if I add a space between the AM/PM (tt) i dont get an error but it also is not the correct time - garbage data. 

    Is there a way to recognize this format when I pull it in via an external table as a datetime format?  Trying to avoid bringing in as a varchar and converting.

    Thanks.




    • Edited by mschandler Thursday, January 2, 2020 9:48 PM clarify
    Thursday, January 2, 2020 9:40 PM

All replies

  • This is strange. The format you have for DATE_FORMAT parses correctly in .Net

    Please provide the error message so that we can troubleshoot further.


    Friday, January 3, 2020 10:47 AM
  • -- This does not work without the space for the AMPM (tt): /* CREATE EXTERNAL FILE FORMAT [NDDFormat_DateTime_5] WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'\t', DATE_FORMAT = N'MM/dd/yyyy hh:mmtt', USE_TYPE_DEFAULT = False)) GO */ --------------------------------- Msg 105022, Level 16, State 1, Line 1 CREATE EXTERNAL FILE FORMAT statement failed because the DATE_FORMAT 'MM/dd/yyyy hh:mmtt' option specified is not valid. Please refer to the documentation to find the correct options. /*************************************************************************************************************************************************************************/ -- This does work /*------------------------ CREATE EXTERNAL FILE FORMAT [NDDFormat_DateTime_5] WITH (FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (FIELD_TERMINATOR = N'\t', DATE_FORMAT = N'MM/dd/yyyy hh:mm tt', USE_TYPE_DEFAULT = False)) GO ------------------------*/ Completion time: 2020-01-03T09:20:02.6132916-05:00


    Rookie mistake, sorry I did not include the error initially.



    • Edited by mschandler Friday, January 3, 2020 3:51 PM
    Friday, January 3, 2020 2:21 PM
  • Could it possibly be a bug in ADW?  Although the documentation does not require a space between sstt, it doesnt work without it.  Based on the table below, I even tried:  

    DATE_FORMAT = N'MM-dd-yyyy hh:mm:sstt',

    Msg 105022, Level 16, State 1, Line 1
    CREATE EXTERNAL FILE FORMAT statement failed because the DATE_FORMAT 'MM-dd-yyyy hh:mm:sstt' option specified is not valid. Please refer to the documentation to find the correct options.


    Per the specifications in the format below, the green box is what I was emulating, of course "[.fff]" being optional...

    

    Friday, January 3, 2020 4:15 PM
  • Kalyan Chanumolu-MSFT

    Were you able to test this?  I believe this to be a bug, trying to get some code complete!  Thanks. 


    Tuesday, January 14, 2020 4:12 PM
  • Hi MSChandler

    The date mapping in polybase is not entirely the same as .NEt and data type mismatches like these do occur, the best way to handle the data would be to stage and save as varchar and once in DW cast as your required data format. One of the best practices for loading data into DW with Polybase is to stage it first.

    Another option to review is to use the COPY INTO command, we handle dates much more efficiently with this and with your file definition which you shared being TEXT which is currently supported by COPY INTO the loading should be really quick and load the date time as expected. 

    https://docs.microsoft.com/en-us/sql/t-sql/statements/copy-into-transact-sql?view=azure-sqldw-latest

    Tuesday, January 14, 2020 11:27 PM
  • In my original post, see above, it is the very Microsoft documentation that calls out the external table format and exact version I am using. If that's not the correct link for external table format, can you please help me provide a link? 

    I have looked at the link  (in my original post above) here that states its for adw external table date formats, I feel like I am missing something. 


    https://docs.microsoft.com/en-us/sql/t-sql/statements/create-external-file-format-transact-sql?view=sql-server-ver15

    Wednesday, January 15, 2020 2:50 AM