Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

transform UNIX date format to ASCII /Windows format

Hello,

I have a data source where tha date and time is under unix format (timstamp: number of seconds since 1970/01/01 00:00:000).

I need to transform this in ASCII timestamp/ Windows Standard. I cannot see a QlikView Function to do this. I can use DADEDIFF when with SQL but I still need to have the result also for csv data sources.

Is anyone aware of a QlikView built-in function? Or maybe a VBScript to calculate?

Thanks in advance,

Regards,

John

1 Solution

Accepted Solutions
Oleg_Troyansky

John,

if Unix format is number of seconds since 1970/01/01, and QlikView format is number of days since 1900/01/01,it's rather simple to calculate:

QlikView date for 1970/01/01 is 25569. From here, add the number of days based on the Unix Date:


QV Date = 25569 + <UnixDate> / 24 hours / 60 minutes / 60 seconds


This formula might be off by a day, play with it before implementing.

cheers,

Oleg

View solution in original post

3 Replies
Oleg_Troyansky

John,

if Unix format is number of seconds since 1970/01/01, and QlikView format is number of days since 1900/01/01,it's rather simple to calculate:

QlikView date for 1970/01/01 is 25569. From here, add the number of days based on the Unix Date:


QV Date = 25569 + <UnixDate> / 24 hours / 60 minutes / 60 seconds


This formula might be off by a day, play with it before implementing.

cheers,

Oleg

Oleg_Troyansky

By the way, I don't recommend implementing this formula in VBScript function (even though it looks cool) - it will slow things down a lot. Once you nail the exact syntax of the formula, either copy it everywhere you need, or use $() - extensions to encapsulate the logic.

cheers,

Oleg

Not applicable
Author

thanks a lot Oleg. It is really simple indeed and logic.

but I didn't think about it...

Thanks again,

Regards,

John