none
Formatting date and time between blobs and SQL database

    Question

  • Hello there! 

    I have a process which dumps files into Azure blobs. Among other things, the blobs contain temporal information in the format "2018-09-05T06:46:28.2485598Z". I'd like to read the data from blobs into a Azure SQL database and have the temporal info maintained basically in any SQL supported datetime format. This should be a routine task, I assume. However, I'm having some trouble with the transformation:

    ErrorCode=UserErrorInvalidDataValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Column 'XXX' contains an invalid value '2018-09-05T06:56:49.0536346Z'. Cannot convert '2018-09-05T06:56:49.0536346Z' to type 'DateTime' with format 'yyyy-MM-dd HH:mm:ss.fffffffz'.,Source=Microsoft.DataTransfer.Common,''Type=System.FormatException,Message=String was not recognized as a valid DateTime.,Source=mscorlib,'

    Any suggestions how to specify the correct format/culture combination - or what else is wrong? I've tried quite a few already - clearly I'm missing something here. Thanks.

    Thursday, September 6, 2018 12:48 PM

All replies

  • Hi,

    For Datetime type, Azure SQL database supports up to three fractional second digits. So I think the problem lies on the column with type Datetime in Azure SQL doesn't support value like: '2018-09-05T06:56:49.0536346Z', which apparently exceeds this limit.

    You could modify the type definition of the database column as Datetime2 (Fractional seconds can optionally be specified using up to seven digits) or DatetimeOFFSET (Fractional seconds can optionally be specified using up to seven digits) as a workaround.

    This doc will give you more details. Thanks.


    Thursday, September 6, 2018 2:05 PM
  • Hi, 

    ADF copy activity uses "yyyy-MM-dd HH:mm:ss.fffffffz", as you see from the error message, as default format when parsing date/time values. Your values are slightly different. Notice that there is a "T" between the date part and the time part. You can override the default format by setting your own in the structure of the source blob dataset. See below document for more details:

    https://docs.microsoft.com/en-us/azure/data-factory/concepts-datasets-linked-services#dataset-structure

    Friday, September 7, 2018 3:16 AM
  • Hello, thanks for suggestions. 

    I'm already using format 'o', which should support the specific timestamp.

    Now, I did an experiment and build similar pipeline from Azure blob storage into exactly similar Azure SQL database using actual data (well, couple of data specimen) --- and voilà: no problems, datetime of the form 2018-09-05T06:56:49.0536346Z was nicely transformed into [datetime2] SQL type.

    Somehow in the original environment the same transformation does not work. Perhaps there is something weird in the original blob storage, some obscure setting? The original is created by Application Insight. 

    Any ideas? Thanks!


    Friday, September 7, 2018 12:12 PM
  • I believe if you already set the format, the error message would have shown it to you. You can share with us the run ID for both the successful and failed ones and we can check the logs for you.
    Saturday, September 8, 2018 5:18 AM
  • Hello and thanks for the reply. For now, I solved the problem by treating the date&time entry as string as far as Azure Data Factory is concerned and allowed AzureSQL do the transformation from this string into datetime2. This works fine. 

    Thanks.


    Tuesday, September 11, 2018 12:56 PM