cancel
Showing results for 
Search instead for 
Did you mean: 

Null Value for Date field in HCP

Hi Expert,

I am facing issue while updating the date field with blank value. I am posting the record in table using XSJS. If I am passing the value as "CHANGEDON":"2017-04-13", in request payload, it is getting inserted with the correct value.

But when I am trying to post the record with blank date value with below different format "CHANGEDON":"00000000", "CHANGEDON":"0000-00-00", "CHANGEDON":"", the value is getting inserted as CHANGEDON = 31.12.-1

Please share your needfull suggestion.

Regards,

Anurag Singh

0 Kudos

hello there,

did you get any solution for this issue ? im currently facing the same problem in my hana db.

Accepted Solutions (1)

Accepted Solutions (1)

Ivan-Mirisola
Product and Topic Expert
Product and Topic Expert

Hi anuragsingh,

DateTime fields do not allow you to store empty or null values.

A Date field should store the value '00000000' when you insert empty values into them

Please check the following documentation:

https://help.sap.com/docs/HANA_SERVICE_CF/7c78579ce9b14a669c1f3295b0d8ca16/3f81ccc7e35d44cbbc595c7d5...

In essence, here is how it should work:

CREATE ROW TABLE T (ID INT, CHANGEDON DATE, CREATEDON DATE);
INSERT INTO T VALUES (1, '', '0001-01-01');
INSERT INTO T VALUES (2, '0000-00-00', '0001-01-01');
INSERT INTO T VALUES (3, '0000-00-00', '0001-01-01');
INSERT INTO T VALUES (4, NULL, '0001-01-01');
INSERT INTO T VALUES (5, '2022-01-01', '2022-01-01');

SELECT * FROM T WHERE CHANGEDON = '00000000';  <-- This will return all rows
SELECT * FROM T WHERE CHANGEDON = ''; <-- This will return all rows
SELECT * FROM T WHERE CHANGEDON IS NULL; <-- This will return all rows
SELECT * FROM T WHERE CHANGEDON IS NOT NULL; <-- This will return only row # 5
If date is being stored with a negative year, then I would open a support ticket so SAP can investigate this issue in detail.

Best regards,
Ivan

Answers (0)