topic transform UNIX date format to ASCII /Windows format in QlikView

transform UNIX date format to ASCII /Windows format

Thu, 07 May 2009 20:11:50 GMT

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

transform UNIX date format to ASCII /Windows format

Oleg_Troyansky — Thu, 07 May 2009 20:37:15 GMT

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

transform UNIX date format to ASCII /Windows format

Oleg_Troyansky — Thu, 07 May 2009 20:43:03 GMT

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

transform UNIX date format to ASCII /Windows format

Thu, 07 May 2009 20:57:56 GMT

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

but I didn't think about it...

Thanks again,

Regards,

John