Conversion from Jullian Date/time stamp to gregorian just date field

[#1]

Dear Qlikjunkies!

I have tried various options on the forum. However it seems all examples only show the conversion fromm date to date not from date/time to date.

Also note below is just an example of what I currently am using on my preceding load on a date/time Julian field so I can link it to my Gregorian calendar script.

LET CurrentJulian = 11978424;
LET Gregorian_Date = date(yearstart(makedate(((($(CurrentJulian) - fmod($(CurrentJulian),1000)) / 1000) + 1900))) + (fmod($(CurrentJulian),1000) - 1));

Any help how I can change the Julian date/time to just Gregorian Date on preceding load would be greatly appreciated.

Below for reference find my calendar script:

LET vDateMax=Num(Date(Makedate(2013,1,1),'DD/MM/YYYY'));

LET vDateMax=floor(monthend(today()));

LET vDateToday = num(today());


Calendar:

LOAD Distinct Date($(vDateMin) + RowNo() - 1,'DD-MM-YYYY') AS Date,

month(Date($(vDateMin) + RowNo() - 1,'DD-MM-YYYY')) AS Month,

year(Date($(vDateMin) + RowNo() - 1,'DD-MM-YYYY')) AS Year,

monthname(Date($(vDateMin) + RowNo() - 1,'DD-MM-YYYY')) AS Monthname,

Week(Date($(vDateMin) + RowNo() - 1,'DD-MM-YYYY')) AS Week


AutoGenerate 1 While $(vDateMin) + IterNo() - 1 <=$(vDateMax);

THANKS IN ADVANCE!

[Gysbert_Wassenaar]

See this document: Non-Gregorian calendars

[#3]

Yea not very helpful. I don't want to make a Julian Calendar. I just want to on preceding load convert a few date/time Julian format to display date only in Gregorian format.

Example Julian: 1196632800

Example Output Required in Gregorian: 01-12-2016

That's it.

[Gysbert_Wassenaar]

1196632800 doesn't look like a Julian Date. Do you know what is actually is? What's the source? What's the unit? Seconds, minutes?

[#5]

It's a unix timestamp in Julian 10 digit format.

That's what makes it so hard for me.

Seems like the few and far between use this.

The value 1368463365 (10-Digit Julian) would be equivalent with 13 May 2013 16:42:45 GMT.

A Unix timestamp is simply the number of seconds since midnight January 1st, 1970 UTC/GMT.

PLEASE HELP!

Every other suggestion has failed due to the length of the timestamp.

Thank you!

[Gysbert_Wassenaar · Accepted solution]

If it's a unix timestamp then it goes like this:

Number of seconds in a day: 60*60*24 = 86400

Number of days since 1-1-1970: Floor(1368463365 / 86400) = 15838

Day number of 1-1-1970 in regular calendar: num(makedate(1970)) = 25569

Date: Date(Floor(1368463365 / 86400) + 25569) = Date(41407) = 13-5-2013

But 1196632800 doesn't work out to 01-12-2016 then, but 02-12-2007