4

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

Jason312
  • 197
  • 1
  • 1
  • 10

2 Answers2

4

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.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Missed the SSIS key item +1 – John Cappelletti Mar 07 '17 at 21:01
  • This the result i got from above code.. very close to what i m looking for but how to get rid of those extra 000000 for time column Date - 2016-11-30 Time - 07:29:21.0000000 – Jason312 Mar 07 '17 at 21:29
  • @alejandrozuleta still unable to get rid of the millisenconds. result from the code Time - 07:29:21.0000000. – Jason312 Mar 07 '17 at 22:04
  • @Jason312, What is the data type of the column where you are inserting the values? How are you visualizating the data in the source? Also bear in mind formating should be handled in the presentation instead of the backend. – alejandro zuleta Mar 07 '17 at 22:12
  • I have tried datetime and time both still unable rid of the milliseconds – Jason312 Mar 08 '17 at 05:44
1

To convert your varchar into a datetime, I would recomment Try_Convert() if 2012+, if not just Convert() would do the trick provided your data is reasonable.

try_convert(datetime,Timestr,103)  --  returns 2016-12-28 16:39:01.000

To use Format()

Declare @Timestr varchar(25) = '28/12/2016 16:39:01'
Select DateTime12 = format(try_convert(datetime,@Timestr,103),'yyyy-MM-dd hh:mm:ss tt')
      ,DateOnly   = format(try_convert(datetime,@Timestr,103),'yyyy-MM-dd')
      ,Time12     = format(try_convert(datetime,@Timestr,103),'hh:mm:ss tt')
      ,Time24     = format(try_convert(datetime,@Timestr,103),'HH:mm:ss')

Returns

DateTime12                DateOnly      Time12         Time24
2016-12-28 04:39:01 PM    2016-12-28    04:39:01 PM    16:39:01
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66