Tuesday, February 14, 2012

DATEADD with 8 byte int?

I have a bigint column called "MillisecondsSince1970" that I need to convert to a date - SSIS is erroring out when I use DATEADD with the 8 byte int (if I use 4 byte it works but the column is bigger than 4 byte). The error is really lame:

[Derived Column [79]] Error: The "component "Derived Column" (79)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "Date" (100)" specifies failure on error. An error occurred on the specified object of the specified component.

Anyone have a way around it... a VB.NET equivalent of DATEADD or something else I can do?

Why not do it in stages? Convert milliseconds to say, days, and then add that to your date via dateadd. Then take the remaining milliseconds and add to the new date field. For each step, cast to INT and then pass into dateadd.

So, 12636598775 milliseconds = 146 days, 22198775 milliseconds

In a derived column, for instance:
days = (DT_I4)(DT_NUMERIC, 30, 8)([millisecond_column] / 1000 / 60 / 60 / 24)

In a second derived column:
newdatecolumn = dateadd("d", [days], [date_column])
finaldatecolumn = dateadd("ms", (DT_I4)([millisecond_column] - ([days] * 24 * 60 * 60 * 1000)),[newdatecolumn])|||Phil that is an excellent idea... I kept trying ways to do it in one object - didn't think about doing it like that. (FYI VB.NET doesn't have milliseconds as a DateInterval so you can't do it in script without doing something like phil suggests)

No comments:

Post a Comment