Search code examples
sql-serverssisetlssis-2012

Is there a better way to parse [Integer].[Integer] style dates in SSIS?

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?

Solution

  • 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: