Question Re: Rules for OData datetime properties in Technology Q&A

Rules for OData datetime properties

Former Member — Mon, 21 Oct 2013 15:32:01 GMT

There have been quite a few posts about problems with Edm.datetime properties in entities. Solutions offered are often on case by case basis, so it's hard to figure out why things have gone wrong and what the remedy is.

This is a short discussion (I hope!) to establish some rules for edm.datetime properties. If there are any exceptions to these rules, or some addtitional rules, please post.

Rule 1

Use the Edm type datetime. Don't maintain dates as edm.string "for convenience"

Rule 2

Make the property nullable if a null value is possible. If there is a need for a value (mandatory) do not make it nullable.

Rule 3

The nullable attribute can only be correctly evaluated if the backend date contains '00000000' as a value. ABAP date values containing spaces (this is allowed unfortunately) will not be treated as null, but also not be able to convert to an edm.datetime - you will get a runtime error from the server.

Rule 4

Be aware of the full property definition. Giving your "date property" a type of edm.datetime alone is not enough to ensure proper conversion during OData parsing. See rules 5, 6 & 7.

Rule 5

If the date property is part of a DDIC structure used for the entity definition, and that component is a DATS (elementary D type), then you can place a D type value in it directly, e.g.

Valid ABAP:

ls_entity-date = sy-datum.

Dates of this type can fall into the "date is null but not valid" trap mentioned in rule 3.

Rule 6

If the date property is not part of a DDIC structure and the entity definition is "freeform", you can also place a D type value in it directly if you assign its backend data typing: e.g..

Dates of this type can fall into the "date is null but not valid" trap mentioned in rule 3.

Rule 7

If the date property is not part of a DDIC structure, the entity definition is "freeform" and there is no backend data typing, the target must be filled as if it were a timestamp; this is because the MPC generated property type is a packed type field. In rules 5 and 6, the MPC generated property type is a D type.

Note that the edm.datetime will contain an additional portion representing microseconds, which is not normally filled unless drawn from a high resolution timestamp (i.e. more detailed than sy-uzeit).

Dates of this type cannot fall into the "date is null but not valid" trap mentioned in rule 3.

Rule 8

You may also use TIMESTAMP as the component type in a DDIC structure entity base instead of DATS. This can be converted as per rule 7.

I'm sure there are more things we can add here: for example, I found that "json format" feed dates can look a bit "odd" - are they right or wrong?

Regards

Ron.

Re: Rules for OData datetime properties

stefan_schreck — Wed, 13 Nov 2013 09:39:40 GMT

Hello Ron (and all),

Note that Edm.DateTime is not part of the V4 specification of odata.org anymore.

To anticipate V4, I recommend to use Edm.DateTimeOffset instead; this is already supported by NetWeaver Gateway.

Regards,

Stefan

Re: Rules for OData datetime properties

Former Member — Wed, 13 Nov 2013 10:11:41 GMT

Thanks Stefan.

Just when we think we've got to grips with the date and time options, it changes again!

Expect some additions to this page when OData 4 comes along then...

regards

Ron.

Re: Rules for OData datetime properties

RalfHandl — Fri, 29 Nov 2013 07:01:49 GMT

Hi Ron,

The only difference between DateTime and DateTimeOffset is that the latter contains time-zoned values, e.g. a trailing Z for UTC.

Without time zone sender and receiver need out-of-band information to correctly interpret the values. DateTime was defined to have an undefined offset, so we removed it from OData 4.0. Which wasn't an easy decision.

Regards!

--Ralf

Re: Rules for OData datetime properties

tejasChouhan — Fri, 23 May 2014 08:57:04 GMT

Hi Ron ,

Thanks. I am facing one new error:

How do we pass datetime in Odata URL as blank, where Date is key field. Here I am using custom RFC.

When i pass as Date=datetime'2014-05-14T00:00:00' I am able to get the values, but I want to pass Date field as blank. How do we do this ??

I tried different formats :

Date=datetime'0000-00-00T00:00:00'

Date=datetime'00-00-00T00:00:00'

Date=datetime''

Date=''

/sap/opu/odata/sap/ZSRV/GetReports(PoNo='12345',Date=datetime'')

I want to pass only one field in the parameter i.e. PoNo. Date field is required as this is input parameter

Regards,

Tejas

Re: Rules for OData datetime properties

Former Member — Fri, 23 May 2014 10:00:30 GMT

Hi Tejas,

If a feed can return a null date then you should be able to send a null date too. Try sending it as m:null="true".

I've not done any updates involving blank dates recently, but that is what I'd be trying first if I were.

Regards

Ron.

Re: Rules for OData datetime properties

tejasChouhan — Fri, 23 May 2014 10:22:08 GMT

Noh..gives me Invalid key predicate.

Re: Rules for OData datetime properties

Former Member — Fri, 23 May 2014 10:44:01 GMT

Tejas,

I've looked at this in the full context, and it doesn't look like a particulary consistent design. It makes no sense to assign a date to part of the key and not use it for updates. You either remove it from the defined key or pass it back in the PUT. Otherwise you are not complying with OData! You can't have an "optional" key field!

Regs

Ron.

Re: Rules for OData datetime properties

tejasChouhan — Fri, 23 May 2014 10:58:15 GMT

Customer wants to check say: how many PO were created on that particular date. So my RFC has that input parameter, and if I am using mapping with RFC, i need to pass it as key value else gives me error .

May be I will have to try converting data type to Edm.String and then redefine the service and try calling manually the RFC

Regards,

Tejas

Re: Rules for OData datetime properties

RalfHandl — Fri, 23 May 2014 11:30:09 GMT

If the ABAP data type is just a date (DATS) without time portion, you will want to use the new type Edm.Date once V4 arrives in your Gateway.

My recommendation: use Edm.DateTimeOffset for time stamps (here the time zone matters) and Edm.DateTime as a work-around for pure date fields. Additionally annotate the date property with sap:display-format="Date", see SAP Annotations for OData Version 2.0 | SCN. This will tell clients that only the date portion is relevant.

Regards!

--Ralf

Re: Rules for OData datetime properties

Former Member — Fri, 23 May 2014 11:34:54 GMT

Sorry that's just the result of awful design

If you want to get help can you open a new thread as this isn't an issue with date format and isn't contributing any value to this thread.

Re: Rules for OData datetime properties

RalfHandl — Fri, 23 May 2014 11:48:24 GMT

A better design would be to have an entity set "PurchaseOrders" with a single key property "PoNo" and a non-key property "Date". Then you can formulate a lot of questions your current design does not allow:

POs created at a given date:

GET ~/PurchaseOrders?$filter=Date eq datetime'2014-05-23T00:00'

Number of POs created at a given date:

GET ~/PurchaseOrders/$count?$filter=Date eq datetime'2014-05-23T00:00'

POs created in a given period :

GET ~/PurchaseOrders?$filter=Date ge datetime'2014-01-01T00:00' and Date le datetime'2014-03-31'

etc.

Re: Rules for OData datetime properties

Aksel — Thu, 10 Jul 2014 13:28:18 GMT

Hi Ron,

I followed your Rules, but I get a Dateformatt error - actually I got the before I found you thread 🙂

I have a Gateway-Hub and a IW_BEP-Backend.

In the Backend an attribute is bound to a structure according to rule 5

Edm.DateTime <-> DATS Length 8

In the service implementation (READ) I'm doing this:

er_entity-gbdat = ls_customer-gbdat.

In debugger it is looking good: er_entity-gbdat = 19530321 (e.g.)

But calling the service leads to the error "Format invalid: Gbdat - Value: 21.03.19"

If changed to Edm.DateTimeOffset I'm getting the error "21.0-3.-19" can't be interpreted as a number...

Seems like the DATS is somewhere converted to its output (21.03.1953) and after this it is put into the Edm.-field...

Do you have an idea what is happening?

Wishes,

Aksel

SP:

Backend:

IW_BEP -> 200 08

Hub:

SAPGW_FND -> 740 07

Re: Rules for OData datetime properties

Former Member — Thu, 10 Jul 2014 14:28:59 GMT

Hi Aksel

The date type assignment in your model may be wrong. Edm.DateTime may imply that the date is type D but generation errors can create a different (incorrect) type. Check the 'type' declaration in the MPC to make sure of the type assignment.

Otherwise, I cannot see why this might consider the date to be invalid.

Regards

Ron.

Re: Rules for OData datetime properties

Aksel — Wed, 16 Jul 2014 12:01:48 GMT

Thank you very much. You brought us on the rigtht track:

In MPC we saw that our field Type GBDAT had a conversion exit inside: PDATE. That lead to the error. We created a new type without conversion exit and it works.

Wishes,

Aksel

Re: Rules for OData datetime properties

grabz — Wed, 07 Aug 2019 08:31:12 GMT

I suggest to proceed with this KBA in case of conversion issues in SAP Gateway:

2819936 - Check Project Consistency button in SEGW results in numerous warnings and/or error messages

Re: Rules for OData datetime properties

Elkern — Tue, 03 Dec 2024 06:41:28 GMT

For me the issue was solved by using Edm.DateTimeOffset

Re: Rules for OData datetime properties

Rakesh12 — Sun, 02 Mar 2025 14:24:52 GMT

Hi Elkern , May i know how to pass the time stamp in gateway client which means there is an input parameter in CDS View with time stamp when i passed the input in odata link it says expected type is 'Edm.Datetimeoffset'?