3

How can I in SSIS combine these two fields into 1 column of type datetime? Both source tables are in datetime and so is the target table.

Dates
2009-03-12 00:00:00.000 
2009-03-26 00:00:00.000 
2009-03-26 00:00:00.000 

 Times
1899-12-30 12:30:00.000 
1899-12-30 10:00:00.000 
1899-12-30 10:00:00.000
user1024008
  • 111
  • 2
  • 6
  • check this one out: http://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server – Davide Piras Nov 14 '11 at 17:28

2 Answers2

6

You need two steps to achieve your goal.

1) First merge both Dates and Times into a single row. I am guessing you have a key to tie the two up, so use this inside a merge join transformation (you will need to sort by this column prior to entering the merge) to create a single row e.g.

Merge Join Transformation

2) Convert the two columns into one inside a derived column transformation with the following casts (DT_DBTIMESTAMP)(SUBSTRING((DT_WSTR,23)Dates,1,11) + SUBSTRING((DT_WSTR,23)Times,12,8))

This should provide you with a new column of datetime to insert into your database e.g.

Merge Join Example

Daryl Wenman-Bateson
  • 3,870
  • 1
  • 20
  • 37
0

A tidier and more efficient way to do calculate the derived column where you add the time to the date is to use the following expression:

DATEADD("Ms",DATEDIFF("Ms",NULL(DT_DATE),[Time]),[Date])

This works in 2 parts: on the inside, the DATEDIFF("Ms",NULL(DT_DATE),[Time]) calculates how many milliseconds the [Time] is past above the null date (i.e. 1899-12-30 00:00:00 in this case), and then the outer part of the expression adds that number of milliseconds to the [Date], e.g. if the time is 01:00:00, that's 3,600,000 milliseconds, so it ends up adding DATEADD("Ms",3600000,[Date]), or adding an hour to the date.

ambrosen
  • 101
  • 2
  • 5