Post reply

SSIS 2012 loading date in format YYYYDDD from file

  • July 26, 2016 at 7:51 pm

    #320184

    Hi

    I am trying to load a file into a table and can not get my head around this one.

    The File contains the date in a string of format YYYYDDD and I can load it as a string but not as a date

    In the Dataflow I am thinking of using a derived column expression where I can build the logic to transform this YYYYDDD into something the database accepts as a date. But I mustn't have had enough coffee yet as I can not get it to work.

    What I am trying to do is substract the year part and add 0101 and than use a dateadd function to add the number of days with a bunch of casts to get the correct datatypes...

    Anyone have done this before that could provide me some pointers/solutions

    Thank you

  • July 26, 2016 at 8:50 pm

    #1892174

    You should be able to convert this answer to ssis derived column script.

    http://stackoverflow.com/questions/31977933/sql-ssis-how-to-convert-from-yyyyddd-format-to-regular-datetime

    Steve.

  • July 26, 2016 at 10:38 pm

    #1892177

    The calculation is quite simple, divide the number by 1000 to get the year and the add the remainder of the division - 1 as days, here is a quick example in T-SQL (as I don't have BIDS or SSDT on the tablet)

    😎

    DECLARE @YYYYDDD INT = 2016186;

    SELECT

    DATEADD(DAY,((@YYYYDDD % 1000) - 1),CONVERT(DATE,CONVERT(VARCHAR(15),(@YYYYDDD / 1000),0) + '0101',112))

    Output

    2016-07-04

  • July 26, 2016 at 11:54 pm

    #1892183

    Thanks for the replies...

    Yes, is very straightforward in TSQL but when I put it in the expression of a derived column I keep on getting errors.

    I just can't seem to get the syntax right

  • July 26, 2016 at 11:59 pm

    #1892184

    Post what you have tried and the errors

    😎

  • July 27, 2016 at 12:32 am

    #1892189

    Check this link

  • July 27, 2016 at 8:22 am

    #1892288

    register-1103039 (7/26/2016)

    Thanks for the replies...

    Yes, is very straightforward in TSQL but when I put it in the expression of a derived column I keep on getting errors.

    I just can't seem to get the syntax right

    Something like this?

    DateAdd("d", (DT_I4) right( @[User::strDate],3 ) -1,(DT_DATE) (left( @[User::strDate],4) + "-01-01"))

    Obviously, you need to replace @[User::strDate] with your column name.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • July 27, 2016 at 10:32 pm

    #1892404

    Thanks that was it...

    Looks so simple looking at it here 🙂

    Now I have added some null checking and error rows redirects as well. Working like a charm.

    This is part of the exercise for my MCSA 70-463 exam and the sample code simply wasn't working.

    The sample code was simply loading it (trying to and failing actually) from YYYYDDD format into date field without any formatting.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply