calculating date seconds start from 01-01-1970

[qlikhans]

In my CRM software dates are represented bij seconds, starting from 01-01-1970. Can anyone help me to recalculate this to a date?

thnx, Hans

[hector]

Hi, i made this test model, check if it works for you

rgds

[qlikhans]

hi hector,

I am testing against cognos and because of that using a personal version of qlikview. can not open your file. still thnak you for the effort so far

rgrds

[hector]

Don't worry my friend!!

here is the script

Let vBASE = num(makedate(1970,01,01));


LOAD
 UnixDate,
 DateToValidate,
 //86400 is the number of seconds for 1 day (24 hrs * 60 mins * 60 secs)
 floor(UnixDate/86400) as Days_Int, //this variable has the number of the days since 1-1-1970
 frac(UnixDate/86400) as Days_Dec, //this variable has the number of hours/min/secs
 timestamp($(vBASE) + (UnixDate/86400)) as NormalDate,
 date($(vBASE) + floor(UnixDate/86400)) as Day,
 time(frac(UnixDate/86400)) as Hour
INLINE [
 UnixDate, DateToValidate
 1262304000, Jan-1-2010 00:00:00 am
 1262307600, Jan-1-2010 01:00:00 am
 1262309400, Jan-1-2010 01:30:00 am
 978313556, Jan-1-2001 01:45:56 am
 915549683, Jan-5-1999 15:21:23 pm
];

use http://www.onlineconversion.com/unix_time.htm to check the values

so, the only thing you must do, is create a table with all the fields and check specially NormalDate field

rgds

[#5]

HOOOO Great script

Thank a lot 😄