[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 844
  • Last Modified:

Derive single datetime2 from separate date and time columns

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?
0
gnf
Asked:
gnf
  • 4
  • 2
1 Solution
 
dbaSQLCommented:
You're going to need to concatenate the two values, and I'm pretty sure you can just do this with a derived column.  

Add the 'Derived Column' transform to your data flow tab, then double-click it.

Drived column name     whatever you want the new column to be named
Drived Column             choose the 'Add as new field' option
Expression                [datefield1]  + " "  + [datefield2]      
Datatype                   Database Date

Remember, the expression is case sensitive.
0
 
gnfAuthor Commented:
I had hoped there would be a better solution than concatenating strings but maybe there isn't.  I use a regular expression in SSIS as follows:

(DT_DBTIMESTAMP2,3)(SUBSTRING([Date],1,4) + "-" + SUBSTRING([Date],5,2) + "-" + SUBSTRING([Date],7,2) + " " + [Time])
0
 
dbaSQLCommented:
Well, there may be another way, but the concatenated value is what I am familiar with.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
gnfAuthor Commented:
Question closed
0
 
gnfAuthor Commented:
Closed question.
0
 
gnfAuthor Commented:
Wished there was a cleaner way, but it seems there isn't.  Have to concatenate strings.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now