sql-serverdatetimessistype-conversionderived-column

SSIS Derive timestamp column into date and time columns

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 enter image description here

Result enter image description here

Solution

  • 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:

    enter image description here

    UPDATE: Avoid miliseconds in DT_DBTIME

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

    Hope it helps.