How to Convert a BigInt to Gregorian date

[Anonymous]

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. 

[Anil_Babu_Samineni]

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?

[Anonymous]

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. 

[rwunderlich]

Those look like unix timestamps. Try this formula:

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

-Rob

[Anonymous]

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. 

[rwunderlich · Accepted solution]

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

[Anonymous]

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,