Post reply

Derived Column Trasnformation for Data conversion

  • December 19, 2013 at 12:31 am

    #301485

    Hi, i am new to SSIS.I learning SSIS,by creating some sample packages.I am loading data from .csv file to SQL server table.In csv file i have one column as varchar format (eg:22Feb2012),but i need to convert into date format as YYYY-MM-DD.Can you help me in creating Expression for this conversion.

    Let me know if you need further details.

  • December 19, 2013 at 12:50 am

    #1674720

    shalini.doss (12/19/2013)

    Let me know if you need further details.

    Is 22Feb2012 the exact format? For single digit days, is it 01 or 1?

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 19, 2013 at 10:17 am

    #1674919

    It is 01 for single digit

  • December 19, 2013 at 1:41 pm

    #1674983

    It should be similar to this:

    (DT_Date)(SUBSTRING("01Feb2012",3,3) + " " + SUBSTRING( "01Feb2012",1,2) + ", " + SUBSTRING( "01Feb2012",6,4))

    You may have to tweak it a bit, but it should look like "Feb 01, 2012" to the converter.

    I don't have time at the moment to create a full test for this, as date conversion can get a bit funky but that's the general idea. If you can't get this to work for you with a bit of tweaking post back and I'll setup an end to end on it.

    This expression works in a quick text field test I did:

    (DT_WSTR,4000)((DT_Date)(SUBSTRING("01Feb2012",3,3) + " " + SUBSTRING( "01Feb2012",1,2) + ", " + SUBSTRING( "01Feb2012",6,4)))

    So I'm pretty confident in it.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 22, 2013 at 7:58 am

    #1675451

    Thanks for the reply,I forgot to mentioned one thing,my source date format is 22-Feb-13.i need to convert this to YYYY-MM-DD.

    Sorry i should have mentioned this correctly first.

  • December 23, 2013 at 1:17 am

    #1675509

    shalini.doss (12/22/2013)

    Thanks for the reply,I forgot to mentioned one thing,my source date format is 22-Feb-13.i need to convert this to YYYY-MM-DD.

    Sorry i should have mentioned this correctly first.

    You should still be able to use Craig's solution, but adjusting the subscripts accordingly to allow for the extra characters.

Viewing 6 posts - 1 through 6 (of 6 total)

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