Rules for OData datetime properties

[Former Member]

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.

[stefan_schreck · Accepted solution]

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

[Former Member · Accepted solution]

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.

[RalfHandl · Accepted solution]

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

[RalfHandl · Accepted solution]

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

[former_member182874]

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

[Former Member]

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.

[former_member182874]

Noh..gives me Invalid key predicate.

[Former Member]

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.

[former_member182874]

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

[Former Member]

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.

[RalfHandl]

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.

[grabz]

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

[Aksel]

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

[Former Member]

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.

[Aksel]

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