2

I'm working on an SSIS ELT script that needs to parse dates from a TSV file that are stored in the format [INTEGER].[INTEGER] (Excel integer dates followed by second since midnight, e.g., 42825.94097; or microseconds since midnight, e.g., 42831.1229166667). I've come up with the following approach:

  1. Derived Column function to split the input into a date part and a time part

  2. Derived Column function to append the parsed dates together, e.g.,

    DATEADD("day",StartTime_Date,DATEADD("second",StartTime_Time,(DT_DATE)"1/1/1900"))

Is there a more elegant way to do this without resorting to a Script Component?

rjzii
  • 14,236
  • 12
  • 79
  • 119

1 Answers1

1

The DT_DATE data type is implemented using an 8-byte floating-point number. Days are represented by whole number increments, starting with 30 December 1899, and midnight as time zero. Hour values are expressed as the absolute value of the fractional part of the number. However, a floating point value cannot represent all real values; therefore, there are limits on the range of dates that can be presented in DT_DATE." Read more

From the description above you can see that you can convert these values implicitly when mapping them to a DT_DATE Column after converting it to a 8-byte floating-point number DT_R8.

Use a derived column transformation to convert this column to 8-byte floating-point number:

(DT_R8)[dateColumn]

Then map it to a DT_DATE column

Or cast it twice:

(DT_DATE)(DT_R8)[dateColumn]

Experiments

i created a SSIS package with one DataFlow Task

The DataFlow Task Contains a Script Component (as Source) that generate one output row (one column of type DT_R8) with the value 42825.94097.

The Script Component is linked to a derived column that convert this column into DT_DATE using the following expression

(DT_DATE)[Column]

the output i get is as shown below

enter image description here

Related answers

I have many answers related with this question:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • Try converting using a derived column with this expression `(DT_DATE)[InColumn]` (i didn't try it). – Hadi May 31 '17 at 17:48
  • What is the source type? – Hadi May 31 '17 at 17:57
  • Try converting this value to float `(DT_R8)[InColumn]` then map it to a `DT_DATE` colunn. Because i think the source column is of type text – Hadi May 31 '17 at 18:09
  • Your solution is good. But i think it is better to achieve it using implicit conversion. – Hadi May 31 '17 at 18:39
  • @rjzii i rewrite my answer to look better. hope this helps – Hadi May 31 '17 at 20:23
  • if these digits are not important you can trim then using `LEFT(...,11)` function. but i think that DT_DATE supports micro seconds – Hadi May 31 '17 at 21:16