Skip to Content
Technical Articles
Author's profile photo Sören Schlegel

Problems with zero dates

Introduction:

There is a very common problem, that while doing a post request or any kind of update containing empty dates are not allowed by default via SEGW based OData-Services.

How to enable this is described here: DateTime field in OData entity – Points to take care when the UI is throwing run-time error. | SAP Blogs

Problem:

But maybe you struggle here anyway, even when using the workaround as described before – but why? Take a look here at the column DEL_DATE:

CDS%20Preview%20on%20TOA01

It looks fine in the ADT Data-Preview, even if 0000-00-00 is not a very nice date.

But testing the OData-Service based on this CDS View returns an error “the Value ____ is no valid Date according to XML-formats for ABAP”:

Error%20in%20/IWFND/ERROR_LOG

Error in /IWFND/ERROR_LOG

Rescue from the past – thank you SE16:

The solution can be found via our old tooling like the good, old SE16 – here we see the true value of DEL_DATE:

The field does not contain any value – its empty, space or even null, but not 0000-00-00 as shown in ADT! And “space” or “empty string” can’t be interpreted as a date! How to fix it? Simply extend your CDS with an easy CASE-Statement, that sets a value OData can handle – like the one mistakenly shown in ADT:

define view entity ZABC
  as select from toa01
{
  key sap_object                 as SapObject,
      @UI.hidden: true
  key object_id                  as ObjectId,
      @EndUserText.label: 'Content Repository'
  key archiv_id                  as ArchivId,
  key arc_doc_id                 as ArcDocId,

      case del_date
      when '' then '00000000'
      else del_date end          as DelDate
}

This should normally not be necessary and is clearly due to a program error, but still: some kind of solution.

Enjoy!

 

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Renzo Colle
      Renzo Colle

      Hi Sören,

      0000-00-00 is not only a not nice date, it is no valid date according to the specification, eg. OData. Storing empty values on the database it not good and a violation similarly. While technically the CDS expression might work the solution has the impact that any operations on this field like a filter gets slow or depending on the data volume won‘t return at all.

      Thus, the much better and proper solution is already supported by Gateway out of the box. The initial date in ABAP is exposed as null in OData. In RAP service binding this works out of the box, in SEGW-based services you need to ensure to define these properties as nullable.

      Long story short: Your solution is a good approach to mitigate corrupt data from the database, but ideally the data is written properly or corrected on the proper layer.

      Kind regards,

      Renzo

      PS: please also check out the data type DATN (instead of DATS) that also is stored on the database as date and fullfils the related constraints already here to prevent corrupt data to be stored.

      Author's profile photo Sören Schlegel
      Sören Schlegel
      Blog Post Author

      Hi Renzo,

      thanks for your feedback. I use RAP, but I need to combine it with RDS and SEGW, because I am using BLOBs on S/4HANA 2021.

      And as I pointed out: This is not a perfect solution, but it is a workaround, if someone needs to build a CDS View on some kind of "legacy" Coding.

      Kind Regards,
      Sören

       

      Author's profile photo Juan Jose Madrid Amezcua
      Juan Jose Madrid Amezcua

      Hi Soren,

       

      Did you try to set the date as null in your service of segw?

       

      Setting it as null will return, null when it is empty instead of trying to convert 00000000 to a date.

       

       

      Best regards,

      JJ

      Author's profile photo Sören Schlegel
      Sören Schlegel
      Blog Post Author

      Hi JJ,

      the property "nullable" only helps while "saving" with CREATEs or UPDATEs as figured out in the linked article. My Problem here is:

      1. READ or QUERY is not possible, because the empty string can not be converted to at least valid XML/JSON
      2. ADT Data Preview is creating a wrong picture, by doing a "fallback" and show 0000-00-00 instead of the problematic empty string.

      Kind Regards,
      Sören

      Author's profile photo Florian Kube
      Florian Kube

      Thank you Sören Schlegel for this blog. I was also facing this issue with RAP.