Convert a string to date in SSIS-CodePudding

I am using SSIS derived column for an ETL process and I need to convert the string "1982 May 06 12:00:00 AM" to YYYY-MM-DD ("1982-05-06")

This is my attempt:

 LEFT(REPLACE(LEFT( [fechaexpedicion] ,11)," ","-"),5)  MONTH([fechaexpedicion] )  SUBSTRING(REPLACE(LEFT( [fechaexpedicion] ,11)," ","-"),9,3)

But the problem is that month doesn't get the month from a string type, so I haven't been able to convert the month to numeric.

Any suggestion?

CodePudding user response：

If you happen to be using SQL Server 2012 or later, it looks like SQL Server's TRY_CONVERT() function can handle your format:

SELECT TRY_CONVERT(date, '1982 May 06 12:00:00 AM');  -- 1982-05-06