For source systems that use milliseconds since the beginning of the “Unix Epoch” for dates (some Unix, Java etc.), I was having some trouble converting them in SQL Server Integration Services with a Derived Column. The goal, as always, is to end up with a usable datetime data type rather than a (still useful for humans but a pain for systems), string.
The expression I came up with is below, there may be a much more elegant way to do this, please feel free to correct me:
SSIS Expression:
DATEADD("DD", ((DT_I8)(<YOURUNIXDATECOLUMN>/1000/60/60/24)), (DT_DBTIMESTAMP)((DT_WSTR, 25)("1970-01-01 00:00:00.000")))
For good measure, here it is in PL/SQL and T-SQL
PL/SQL
SELECT TO_DATE( ( TO_DATE ('1970-01-01 00:00','YYYY-MM-DD HH24:MI') + (YOURUNIXDATECOLUMN/1000/60/60/24) ) ,'YYYY-MM-DD HH24:MI')FROM YOURTABLE AS DATEFROMUNIX
T-SQL
SELECT DATEADD(DD, (YOURUNIXDATECOLUMN/1000/60/60/24), '1970-01-01 00:00:00') FROM YOURTABLE AS DATEFROMUNIX
Obviously, you can divide by 86,000,000 instead, but I think it makes your code easier to read (clearly converting to days from milliseconds). Also obviously, the T-SQL could be made into a UDF (on the subject of that, I remain cowardly neutral -- I do, however, have some pretty strong opinions about UGBs).
Note: I cribbed the PL/SQL from Jive Software’s doc on converting their timestamps, so thanks for documenting that!
Also Note: If I were giving out prizes for things that make my life difficult, datetime data types would be eligible for a truckload of factory-second tube socks, or slightly dented tins of peaches this week.
0 comments:
Post a Comment