| Author |
Message
|
| petervh1 |
 Posted: Wed Sep 01, 2021 7:03 am Post subject: Create JSON date field |
 |
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
I'm trying to create a JSON structure that contains (amongst other fields) the following:
| Code: |
"Field1StartDate":"/Date(-62135769600000)/",
"Field2EndDate":"/Date(253402214400000)/",
|
I don't see a mention of a date field in:
How can I do this?
The value of Field1StartDate is a date minus the number of milliseconds since 1/1/1970, and Field2EndDate is 31/12/9999 minus the number of milliseconds since 1/1/1970.
Thanks |
|
| Back to top |
|
 |
| timber |
| Posted: Wed Sep 01, 2021 11:58 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
Converting a DATE to an INTEGER is not a JSON-specific task - that's why you did not find the information under the JSON-specific topics.
You have to do it like this:
| Code: |
DECLARE epoch TIMESTAMP '1970-01-01 00:00:00';
DECLARE secondsSince1970 INTERVAL (CURRENT_TIMESTAMP - epoch) SECOND;
DECLARE timeInMilliseconds CAST(secondsSince1970 AS INTEGER) * 1000; |
I have not tested that code, so you may need to adjust some of the details to make it work. |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Sep 02, 2021 12:45 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
Thanks for that.
Am I correct in saying that I can't cast the millisecond value derived from the code you posted to a DATE format in the JSON output structure. (I need to send this JSON structure to SAP, and SAP expects a DATE type field here) |
|
| Back to top |
|
|
| timber |
| Posted: Thu Sep 02, 2021 5:20 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
I'm confused now. I thought you wanted to send a millisecond value in the JSON.
Please can you provide
- example input value(s)
- corresponding JSON output data, exactly as it needs to appear. Please use [code] tags (see buttons above) to ensure that it appears exactly as you intended. |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Sep 02, 2021 5:51 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
The JSON output needs to look like the Code section in my first post on this thread.
I just wanted confirmation that this cannot be a Date field type before I go back to the systems designer/analyst to say that this field is invalid in its current form. |
|
| Back to top |
|
|
| timber |
| Posted: Thu Sep 02, 2021 6:25 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
So you want your output JSON to look like this:
| Code: |
{
"Field1StartDate":"/Date(-62135769600000)/",
"Field2EndDate":"/Date(253402214400000)/"
} |
It looks a bit strange, but it is a valid JSON document. So I don't understand what you mean by this:
| Quote: |
| this cannot be a Date field type |
and this
| Quote: |
| this field is invalid in its current form |
Are you absolutely sure that SAP wants this exact JSON format? |
|
| Back to top |
|
|
| petervh1 |
| Posted: Thu Sep 02, 2021 6:43 am Post subject: |
|
|
Centurion
Joined: 19 Apr 2010
Posts: 135
|
| Quote: |
So you want your output JSON to look like this:
Code:
{
"Field1StartDate":"/Date(-62135769600000)/",
"Field2EndDate":"/Date(253402214400000)/"
} |
Yes.
| Quote: |
| this cannot be a Date field type |
I meant that I can't CAST this as a DATE
| Quote: |
| Are you absolutely sure that SAP wants this exact JSON format? |
That's what I was given, but I'm starting to doubt it. Hence my initial query on this forum before I push back to the designer. |
|
| Back to top |
|
|
| timber |
| Posted: Thu Sep 02, 2021 10:30 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
I don't understand why you are asking about CASTing this value to an ESQL DATE. You already have a number which can easily be converted to a string of digits in the JSON output. Why do you want to turn it back into a DATE?
But I agree - you should be asking for a sample document or else the official SAP data format specification. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Sep 02, 2021 8:21 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
And remember in Java the date under the form you show is a long not an int.
There are reasons for that, and you may need to use a double or float in ESQL.
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| timber |
| Posted: Thu Sep 02, 2021 11:36 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| In ESQL, an INT/INTEGER is a 64-bit value, so should be fine. |
|
| Back to top |
|
|
|
|
