none
How to format datetime properly in copy activity. Receiving error when copy activity due to formatting

    Question

  • I have a copy activity from blob to sql server.  During this activity, I get an error 

    Message=Column 'sys_created_on' contains an invalid value '"2018-10-30 20:12:25 PDT"'. Cannot convert '"2018-10-30 20:12:25 PDT"' to type 'DateTime'.,

    My question is, how do I get this formatted correctly. I think it is the PDT that needs to be scrubbed. I see a format option, but not sure what to put in there. Any help is appreciated


    josh

    Wednesday, October 31, 2018 2:35 PM

All replies

  • Hi Josh,

    Please reference the following documentation for datetime (Transact-SQL)

    Usage DECLARE @MyDatetime datetime

    CREATE TABLE Table1 ( Column1 datetime )

    Wednesday, October 31, 2018 5:56 PM
    Moderator
  • Hi,

    You are right you will need to scrub PDT but also at the end it could have zero to three digits that represent fractional seconds

    Datetime looks like:

    2016-12-01 12:32:00.000

    In that case what you have:

    2018-10-30 20:12:25 PDT

    Should look like this:

    2018-10-30 20:12:25

    or this:

    2018-10-30 20:12:25.007

    Regards,

    Alberto Vega

    • Proposed as answer by Alberto Vega (Azure) Wednesday, October 31, 2018 8:56 PM
    • Unproposed as answer by JoshJJames Wednesday, October 31, 2018 9:41 PM
    Wednesday, October 31, 2018 8:53 PM
  • So how do I enter that in the format box in Data Factory? What's the syntax?

    josh

    Wednesday, October 31, 2018 9:42 PM
  • Hi Josh,

    Could you try as Format:

    "yyyy-MM-dd HH:mm:ss P\\D\\T"

    Try without Culture, but if you have problems try:

    en-US



    Wednesday, October 31, 2018 11:58 PM
    Moderator