Derive single datetime2 from separate date and time columns
Posted on 2011-02-25
I am importing a vast amount of data into Sql Server with SSIS. In the input data, which is in CSV format, there are separate date and time columns. The time has millisecond precision. I want to derive a single datetime2 output column.
I have tried in SSIS to use the derived column transformation as follows:
(DT_DBTIME2,3)[Date]+ (DT_DBTIME2, 3) [Time]
But this cannot be evaluated.
Does anyone have an idea how to simply derive a single datetime2 column from separate input date and time columns in a CSV?
I thought of casting date and time as strings, concatenating them with a space, and then casting the result as a datetime2(3) column. This seems inelegant and slow however. Any better approaches?