Technical Articles
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:
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 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!
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.
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
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
Hi JJ,
the property "nullable" only helps while "saving" with CREATEs or UPDATEs as figured out in the linked article. My Problem here is:
Kind Regards,
Sören
Thank you Sören Schlegel for this blog. I was also facing this issue with RAP.