SSIS: convert ISO 8601 string to datetime column

Question

I have a CSV falt file I'm trying to import using SSIS. The date string is in the format 20170215T000002 - this looks like ISO 8601, so I'm pretty sure I should just be able to map it to a datetime column type in SQL, but I lose all of the time data and just get the date component (correctly parsed).

Any advice on how to get this text element into a datetime format in SQL?

Tag your question with the database you are using. – Gordon Linoff Mar 05 '17 at 16:17 — Gordon Linoff, Mar 05 '17 at 16:17

Hadi · Accepted Answer · 2017-03-05T16:48:30.573

3

Assuming that the date column name is inColumn

In the DataflowTask, Add a script component , Mark inColumn as input column, add a new Output column outColumn with dataType DT_DBTIMESTAMP

In the script, in Input0_ProcessInputRow sub use DateTime.ParseExact Function as follow:

Row.inColumn =  DateTime.ParseExact(Row.inColumn,"yyyyMMddTHHmmss",New System.Globalization.CultureInfo("en-GB"))

edited Mar 05 '17 at 16:48

answered Mar 05 '17 at 16:35

Hadi

36,233
13
65
124

score 1 · Answer 2 · answered Mar 05 '17 at 16:19

1

I think ISO 8601 has colons for the time separator.

Assuming that you are using SQL Server, this should work:

select convert(datetime,
               stuff(stuff(col, 14, 0, ':'), 12, 0, ':'),
               126)

answered Mar 05 '17 at 16:19

Gordon Linoff

1,242,037
58
646
786

It would seem it does, but using the suggestion from @Hadi above, I managed to get it to work. – Gavin Hill Mar 05 '17 at 17:40

SSIS: convert ISO 8601 string to datetime column

2 Answers2