How to format a OData v2 Edm.DateTime column

Use Case

In many cases, if you are using an OData v2 entity with a column of type Edm.DateTime, you will need to some at some point construct a proper OData query string. This can be a bit of a challenge as if you don't know the correct syntax for the Edm.DateTime type.

/sap/opu/odata/sap/ZGW_JOB_CLASSIFICATION_SRV/JobSet?$format=json&$filter=BeginDate le datetime'2019-08-02T04:00:00' and EndDate ge datetime'2019-08-02T04:00:00'&$select=JobCode,Title,Level,PersonSubArea,Link

Note: At first glance, you might notice that this query string has a *DateTime * prefix in front of the timestamp value.

/sap/opu/odata/sap/ZGW_JOB_CLASSIFICATION_SRV/JobSet?$format=json&$filter=BeginDate le datetime'2019-08-02T04:00:00' and EndDate ge datetime'2019-08-02T04:00:00'&$select=JobCode,Title,Level,PersonSubArea,Link

buried in the bowels of SAPUI5 framework they have an excellent helper library to help with adding such a prefix.

Library

sap.ui.model.odata.odatautils

Note: In order to use this library in your controller you'll need to add it to your sap.ui.define array like so "sap/ui/model/odata/ODataUtils".

Method

formatValue( date, EdmType)

Example Usage

ODataUtils.formatValue(beginDate, "Edm.DateTime")

As you will see in the below example there is one caveat, the OData query with timestamp needs to be URLencoded so in order to do that we need to use the built-in javascript function encodeURIComponent()

Example :

var sBindUrl = "/JobSet(BeginDate=" + encodeURIComponent(ODataUtils.formatValue(beginDate, "Edm.DateTime")) +  
",JobCode='" + oEvent.getParameter("arguments").jobCode + "')?$expand=JobDescriptionSet" +  
"&$filter=BeginDate le " + encodeURIComponent(ODataUtils.formatValue(beginDate, "Edm.DateTime"));