I have date in format of 22-Mar-2023 08.01.44 PM(EST)
I Want to remove (Est) part of the date but it fails
Trying this doesn’t work
SUBSTRING([Data Date],1,2) + "-" + (SUBSTRING( [Data Date],4,3) == "JAN" ? "01" :SUBSTRING( [Data Date],4,3) == "FEB" ? "02" : SUBSTRING( [Data Date],4,3) == "MAR" ? "03" :SUBSTRING( [Data Date],4,3) == "APR" ? "04" :SUBSTRING( [Data Date],4,3) == "MAY" ? "05" : SUBSTRING( [Data Date],4,3) == "JUN" ? "06" :SUBSTRING( [Data Date],4,3) == "JUL" ? "07" :SUBSTRING( [Data Date],4,3) == "AUG" ? "08" :SUBSTRING( [Data Date],4,3) == "SEP" ? "09" : SUBSTRING( [Data Date],4,3) == "OCT" ? "10" : SUBSTRING( [Data Date],4,3) == "NOV" ? "11" : SUBSTRING( [Data Date],4,3) == "DEC"? "12") + "-" + SUBSTRING([Data Date],8,4)
Looks like some syntax error
"Expression cannot be parsed"
>Solution :
The REPLACE Expression shall work for you in SSIS Derived Column transformation
replace all occurrences using :
(DT_WSTR, 20)REPLACE([Data Date], "(EST)", "")
OR
The below one using LEFT Expression
LEFT([Data Date],LEN([Data Date])-5)
This will extract the left part of the Data Date column excluding the last 5 characters