Discussion board where members can get started with QlikView.
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=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!
Go to 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
See this document: Non-Gregorian calendars
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
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?
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.
Every other suggestion has failed due to the length of the timestamp.