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

Question

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?

Mazda

I have difficulty understanding the format. Can you explain what e.g. date/time 42825.94097 represents? – Trichromatic

@TheEsisia I updated the question – Mazda

@Mazda just use a derived column with the following expression (DT_DATE)(DT_R8)[dateColumn] take a look at my answer update – Impenitent

Answer 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)

Impenitent

Try converting using a derived column with this expression (DT_DATE)[InColumn] (i didn't try it). – Impenitent

What is the source type? – Impenitent

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 – Impenitent

Your solution is good. But i think it is better to achieve it using implicit conversion. – Impenitent

@Mazda i rewrite my answer to look better. hope this helps – Impenitent

if these digits are not important you can trim then using LEFT(...,11) function. but i think that DT_DATE supports micro seconds – Impenitent