How to return empty DateTime from Illuminator OData Service

[philipp_gembe_eh]

Dear experts

We are using IlluminatorOData Service to retrieve Date from a SQL Query Template as JSON.

/XMII/IlluminatorOData/QueryTemplate?QueryTemplate=<Query Template Path>&$format=json

One of the columns is defined as DateTime type and returned by the OData service as JSON-stringifyed Rowsets/Rowset/Row object:

{ "start_date" : "/Date(<ms_since_epoch>)/" }

However, if the source field in the database is null, OData service does not return an empty DateTime, instead MII populates the result with the current timestamp.

In contrast, if we call the very same SQL Query Template as XML output, we receive the well-known placeholder:

<start_date>TimeUnavailable</start_date>

My question is, how do we prevent MII OData service to return a default DateTime value, if the source value is null?

We expect it to be null, blank or "TimeUnavailable", even "/Date(0)/" could work...

[former_member330827 · Accepted solution]

Philipp,

Consider the following MSSQL query executed from a transaction using a SQL query template in MII:

DECLARE @dateExample TABLE (id INT IDENTITY(1, 1), date DATETIME)
INSERT INTO @dateExample (date) VALUES (GETDATE())
INSERT INTO @dateExample (date) VALUES (NULL)

SELECT id,
	date,
	ISNULL(date, '') AS emptyDate,
	ISNULL(date, '1970-01-01 00:00:00') AS nullAsEpochDate,
	ISNULL(CONVERT(VARCHAR(23), date, 21), '') AS convertedDate21,
	ISNULL(CONVERT(VARCHAR(23), date, 126), '') AS convertedDate126,
	ISNULL(CONVERT(VARCHAR(24), date, 127), '') AS convertedDate127
FROM @dateExample

When returned using the IlluminatorOData service against an xacuteQuery:

"Row": {
	"results": [
		{
			"__metadata": {
				"uri": "http://mii.domain.com:50000/XMII/IlluminatorOData/Row(1)",
				"type": "QueryTemplate.Row"
			},
			"id": 1,
			"date": "/Date(1530536140000)/",
			"emptyDate": "/Date(1530536140000)/",
			"nullAsEpochDate": "/Date(1530536140000)/",
			"convertedDate21": "2018-07-02 12:55:40.230",
			"convertedDate126": "2018-07-02T12:55:40.230",
			"convertedDate127": "2018-07-02T12:55:40.230",
			"RowId": 1
		},
		{
			"__metadata": {
				"uri": "http://mii.domain.com:50000/XMII/IlluminatorOData/Row(2)",
				"type": "QueryTemplate.Row"
			},
			"id": 2,
			"date": "/Date(1530536142380)/",
			"emptyDate": "/Date(-2208988800000)/",
			"nullAsEpochDate": "/Date(0)/",
			"convertedDate21": null,
			"convertedDate126": null,
			"convertedDate127": null,
			"RowId": 2
		}
	]
}

If you are using MSSQL, you most likely want to use ISNULL(date, '1970-01-01 00:00:00') on the date field to return "/Date(0)/" for null values. By default, NULL values return as "1900-01-01 00:00:00.000" or "/Date(-2208988800000)/" for some reason. If you would rather format dates on the SQL side and return them as a string, you could also do that as shown above. This will return date values as SQL formatted strings instead of /Date()/ values and NULL dates as null.

I hope this helps.

Eric

[philipp_gembe_eh · Accepted solution]

Hi Eric,

thanks for your answers. We already discussed the "string" approach. However, this leads to additional parsing afford in the odata-consuming web frontend. Plus, you need to find a project-wide agreement on the format. The same goes for the "Date(0)" approach - you always need to implement logic to interprete this "invalid" date. This might work for most applications, but not for all.

Both solutions become very dirty, if you use SQL-Querys in Query-Mode to access them directly using OData (without redirecting through a XacuteQuery). In this case, you need to use the given MII Workbench-UI to build them - urgh...

Kind regards,
Philipp

edit: Still, I consider providing the current date -if the database value is null- as a bug, right?

[former_member330827 · Accepted solution]

I would agree that this could be considered a bug. In OData services called from our SAP ECC environment, I verified date fields returned as null or "/Date(#)/" in JSON format from the ABAP code. I would think that SQL queries from MII should perform the same way, although in my experience MII never returns the key word null. I have seen NA, ---, and TimeUnavailable as null values returned from SQL NULL in MII (not specific to the OData service).