I have the following data:
12APR19 0644 and I want to combine/transform these in SSIS so they appear in my final destination as:
2019-04-12 06:44:00.00
I've tried using (DT_DBTIMESTAMP) [DateColumn] [TimeColumn] but this gives me a generic error. Anyone help please?
First transform your string to dd mmm yyyy hh:mm then use the type case dt_date:
(DT_DATE)"12 APR 2019 06:44"
returns:
4/12/2019 6:44:00 AM
which you can further transform get the format you want. OTOH if your destination is a SQL Server database, you can do:
(DT_WSTR, 20)(DT_DBTIMESTAMP)"12 APR 2019 06:44"
which yields
2019-04-12 06:44:00
You can get away with a single stuff()
Example
Select AsDateTime = try_convert(datetime,stuff('12APR19 0644',11,0,':'))
Returns
AsDateTime
2019-04-12 06:44:00.000
Assuming that the column name is DateColumn, you can use a derived column with the following expression:
"20" + SUBSTRING([DateColumn],6,2) + "-" +
(SUBSTRING( [DateColumn],3,3) == "JAN" ? "01" :
SUBSTRING( [DateColumn],3,3) == "FEB" ? "02" :
SUBSTRING( [DateColumn],3,3) == "MAR" ? "03" :
SUBSTRING( [DateColumn],3,3) == "APR" ? "04" :
SUBSTRING( [DateColumn],3,3) == "MAY" ? "05" :
SUBSTRING( [DateColumn],3,3) == "JUN" ? "06" :
SUBSTRING( [DateColumn],3,3) == "JUL" ? "07" :
SUBSTRING( [DateColumn],3,3) == "AUG" ? "08" :
SUBSTRING( [DateColumn],3,3) == "SEP" ? "09" :
SUBSTRING( [DateColumn],3,3) == "OCT" ? "10" :
SUBSTRING( [DateColumn],3,3) == "NOV" ? "11" :
SUBSTRING( [DateColumn],3,3) == "DEC"? "12":"")
+ "-" SUBSTRING([DateColumn],1,2) + " " + SUBSTRING([DateColumn],9,2) + ":" + SUBSTRING([DateColumn],11,2) + "00.00"
Input
12APR19 0644
Result
2019-04-19 06:00:00.00
Note that if the output column data type is date then you need to cast the expression above to (DT_DATE) or (DT_DBTIMESTAMP)
You can add a script component and use the following line of code to convert this column (assuming that input column is DateColumn and output column is OutDateColumn)
Row.OutDateColumn = DateTime.ParseExact(Row.DateColumn,"ddMMMyy HH:mm",System.Globalization.CultureInfo.InvariantCulture);
If the output is a string column then use the following code:
Row.OutDateColumn = DateTime.ParseExact(Row.DateColumn,"ddMMMyy HH:mm",System.Globalization.CultureInfo.InvariantCulture).ToString("yyyy-MM-dd HH:mm:ss.ff);;
