April 7, 2020 at 10:58 am
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.
April 7, 2020 at 11:43 am
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
April 10, 2020 at 3:24 pm
I've never much enjoyed building out these complex expressions in SSIS.
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.
April 10, 2020 at 4:11 pm
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
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy