Convert date value

  • pwalter83

    SSChampion

    Points: 14557

    Hi,

    I have a requirement to convert a date value like '7th March 2020'  to '2020-03-07'.

    However, the problem is the datatype for the date column is varchar and this needs to be achieved in SSIS.

    Can somebody please help on this ?

    Thanks.

  • John Mitchell-245523

    SSC Guru

    Points: 148747

    What have you tried so far?  I imagine if you replace the "th ", "nd " or "st " with " ", the CONVERT function in SSIS would be able to handle it, wouldn't it?

    John

  • BTylerWhite

    Default port

    Points: 1416

    I've never much enjoyed building out these complex expressions in SSIS.

    John Mitchell-245523 wrote:

    What have you tried so far?  I imagine if you replace the "th ", "nd " or "st " with " ", the CONVERT function in SSIS would be able to handle it, wouldn't it?

    John

    John's suggestion is correct, also just make sure you throw in "rd" as well.

    I created a variable named "sDate" with a String Data type. It's value is set to "7th March 2020".

    I used the following (quite lengthy) expression to convert this into the "YYYY-MM-DD" string format:

    (DT_STR, 4, 1252) DATEPART("YEAR", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("MONTH", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")), 2) + "-" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("DAY", (DT_DATE) REPLACE(REPLACE(REPLACE(REPLACE(@[User::sDate], "st", ""), "nd", ""), "rd", ""), "th", "")), 2)

    Hopefully that helps as at least a starting point.

  • Jeff Moden

    SSC Guru

    Points: 996502

    Recreating such a thing in yet another "string format" would go against my grain.  Why not convert it to a proper DATE or DATETIME datatype?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

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

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