convert excel formula =reporteddate/3600/24+DATE(1970,1,1) to a ssis derived column formula

Question

Can someone please guide on how to convert the excel formula  =reporteddate/3600/24+DATE(1970,1,1)  to a ssis 2008r2 derived column formula.

An example value for reporteddate would be  1328192834

Thanks in advance.

Answer 1

Hi,

The reportdate you are having is unixtimestamp.use following expression in derived column.

 DATEADD("second",reportdate,(DT_DATE)("1970-01-01"))

OR

use following statement in your source query

select DATEADD (SECOND ,1328192834,'1970-01-01') as reportdate

---

Bharath T

Answer 2

Thank you very much for your reply, that did the trick,  I am not near my pc atm, but I just had to change your syntax slightly (I think it was) select DATEADD ("S" ,1328192834,'1970-01-01') as reportdate

Answer 3

Don't forget to take the timezone into account

---

Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu