Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • 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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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