SSIS Derive timestamp column into date and time columns

Question

I have this Timestr in varchar 28/12/2016 16:39:01

How to Derived the column into date and time in SSIS

Desired result

Date 2016-12-28 in Date format

Time 16:39:01 in time format

This is what i have in SQL so far

SELECT FORMAT(CAST((SUBSTRING(TimeStr,12,8)) AS DATETIME),'hh:mm:ss tt') AS Time

Code

Result

Answer 1

Using SSIS expressions you can try:

DATE

(DT_DBDATE)(SUBSTRING(Timestr,7,4) + "-" + 
SUBSTRING(Timestr,4,2) + "-" + SUBSTRING(Timestr,1,2))

TIME

(DT_DBTIME)(SUBSTRING(Timestr,12,8))

Your Derived Column settings should look like this:

UPDATE: Avoid miliseconds in DT_DBTIME

(DT_DBTIME2,0)(SUBSTRING(Timestr,12,8))

Hope it helps.