Convert date value

Question

Convert date value

pwalter83

SSChampion

Points: 14575
More actions
April 7, 2020 at 10:58 am

#3740990

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.

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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 SSCommitted Points: 1569 More actions · Answer 2

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: 1003863 More actions · Answer 3

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)