Is there a better way to parse [Integer].[Integer]

问题:

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?

回答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

Related answers

I have many answers related with this question:

• CAST vs ssis data flow implicit conversion difference
• SSIS Source Format Implicit Conversion for Datetime
• SSIS Error importing Excel Date (truncation error)