Convert date value


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 ?


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


Yes, all the dates follow this pattern.

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

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:

Take a look here also:

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