convert json date format to dd/mm/yyyy

[Bendikl]

Hi,

Im trying to get the dd/mm/yyyy format for my date that i get through a REST connection, however every date comes in json format like this: /Date(1667378999417-0000)/ 
I have tried various methods of converting the date but nothing seems to do the trick, have anyone encountered this issue before?

Brg,

Ben

[Lucas_Gatling]

Good afternoon Bendiki.

JSON does not know anything about dates. What .NET does is a non-standard hack/extension. The problem with dates in JSON and really JavaScript in general – is that there's no equivalent literal representation for dates. In JavaScript following Date constructor straight away converts the milliseconds since 1970 to Date as follows:

var jsonDate = new Date(1297246301973);

Then let's convert it to js format:

var date = new Date(parseInt(jsonDate.substr(6)));

The substr() function takes out the /Date( part, and the parseInt() function gets the integer and ignores the )/ at the end. The resulting number is passed into the Date constructor .

[Bill_Britt]

Hi,

To add on to what @Lucas_Gatling  said, you can also play around with this in excel, to get the format.

[Bendikl]

Hi,

Thank you! 

I am pretty new to Qlik, but i tried adding it to the edit script and it didnt seem to read the code, is there another place within Qlikview i need to put this?

brg,

Ben

[Bill_Britt]

Hi,

You have to date the field like this.

Date(FieldName, 'MMM-YYYY') as NewFieldName

[Bill_Britt]

Hi,

Looking back at this, and searching, I can't find any date format that matches the above. Are you sure this is a date field? What system is it from?

[Bendikl]

Hi,

Yeah i tried formating it the way you suggested but it didnt work..

It's data from Puzzel (https://developer.puzzel.com/#/) through a REST connection.

This is how the date is displayed in the browser

[Bill_Britt · Accepted solution]

OK, this is called  Epoch Unix Timestamp. You can find an example and a running clock at https://www.unixtimestamp.com/?unixTimestampInput=%7B%7B%7Bs%7D%7D%7D

You will need to strip the -0000 off it and use  timeStamp(Makedate(1970,1,1)+ 1668085945 /24/60/60 + Date((120/24/60) , 'YYYY-MM-DD hh:mm:ss'))  to convert it