Convert date value

1

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.

2

Do all of the dates in the table follow exactly that pattern?

1st
2nd
3rd
5th

3

Yes, all the dates follow this pattern.

4

you can try something like this, substitute the column name for the variable

declare @s varchar(30) = '7th March 2020'

Select cast(Concat( m , '-', case when IsNumeric(substring(d,1,2)) = 1 then left(d,2) else left(d, 1) end, '-', y ) as datetime)
from (
select y = Right(@s, charIndex(' ', Reverse(@s)) - 1),
	   m= Month(Cast(substring(@s, charIndex(' ', @s) + 1,  charIndex(' ', @s, CharIndex(' ', @s) + 1) - charIndex(' ', @s) -1   ) + ' 1 2010' as DateTime)),
	   d = substring(@s, 1, charIndex(' ', @s) - 1)) v
5

Since this must be done in SSIS - I would recommend using a script component/task to convert the string to a valid date.

You can start here: https://docs.microsoft.com/en-us/dotnet/standard/base-types/parsing-datetime

Take a look here also: https://www.techiedelight.com/convert-string-to-datetime-csharp/

I would not attempt to do this in an SSIS expression.