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')AS DATEFROMUNIXFROM YOURTABLE
T-SQL
T-SQL's DATEADD makes it easy to work with the date without having to convert the number.
Add Milliseconds directly
SELECT DATEADD(ms, (YOURUNIXDATECOLUMN), '1970-01-01 00:00:00') AS DATEFROMUNIX FROM YOURTABLE
SELECT DATEADD(DD, (YOURUNIXDATECOLUMN/1000/60/60/24), '1970-01-01 00:00:00') AS WholeDaysDATEFROMUNIX FROM YOURTABLE
If you convert a number you know should be relatively recent and it doesn't budge from 1970, it's probably in seconds.