topic Re: How to Convert a BigInt to Gregorian date in QlikView

How to Convert a BigInt to Gregorian date

Anonymous — Tue, 17 Oct 2017 14:05:10 GMT

I'm operating on QSense with a connection to a MYSQL data base.

In my data model, every date fileds are MYSQL BigInt.

In the QSense stage scripting I need to convert them to a gregorian date format.

Would you mind helping me to figure it out ?

Regards!

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

Re: How to Convert a BigInt to Gregorian date

Anil_Babu_Samineni — Tue, 17 Oct 2017 15:32:02 GMT

What was the format you are expecting?

For example, the International Organization for Standardization (ISO) format is YYYY-MM-DD. 2013-12-14 is equivalent to the calendar date December 14th 2013. Are you expecting similar that?

Re: How to Convert a BigInt to Gregorian date

Anonymous — Tue, 17 Oct 2017 16:45:35 GMT

I need to convert them to a date or datetime format easy to read. For example:

a) 17/10/17                             DD/MM/YY
b) 17/10/2017                         DD/MM/YYYY
c) 17-10-17 11:51                   DD/MM/YY HH:MM
d) Oct 17, 2017 11:51 AM      Month DD, YYY HH:MM AM

Thanks in advanced.

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

Re: How to Convert a BigInt to Gregorian date

rwunderlich — Wed, 18 Oct 2017 00:40:15 GMT

Those look like unix timestamps. Try this formula:

TimeStamp(Makedate(1970,1,1)+ (unixtime/1000) /24/60/60)

-Rob

Re: How to Convert a BigInt to Gregorian date

Anonymous — Wed, 18 Oct 2017 13:13:02 GMT

hi Rob,

you're right. But I neet to convert from Unix Timestamp to Gregorian format date.

For example: How to convert 1506623932679 to "28/09/2017 15:38" or "28/09/2017" or "Sep 28, 2017 03:38 PM" ?

Thanks in advanced

Ulises Yair Ogaz | Analista Técnico/Desarrollador JDE - SISTEMAS

Laboratorios Casasco S.A.I.C.

Re: How to Convert a BigInt to Gregorian date

rwunderlich — Wed, 18 Oct 2017 16:17:24 GMT

Hi Ulises,

The expression I gave does just that, converts from unix format to gregorian in your default format. If you want to use an output format other than default, add the optional format mask to the TimeStamp() function:

TimeStamp(Makedate(1970,1,1)+ (unixtime/1000) /24/60/60, 'MMM DD, YYYY hh:mm')

-Rob

Re: How to Convert a BigInt to Gregorian date

Anonymous — Wed, 18 Oct 2017 19:28:33 GMT

Thank you very much, Rob. Finally, I've get it.

Sintaxis:

TimeStamp(Makedate(1970,1,1)+(CREATEDTIME/1000) /24/60/60, 'MMM DD, YYYY hh:mm') as MiFechaTStampPrueba99,