My task is to convert a ABAP style date (i.e. 2017-11-20 which is represented as string "20171120") to a HANA date via sql script. This can easily be done by:
select to_date('20171120','YYYYMMDD') from dummy;
But there is another requirement: if the abap date is initial (value '00000000') the database shall store a null value. I have found a working solution: I replace the potential initial date '00000000' with 'Z' and trim the string to null if only 'Z' is found:
select to_date(trim(leading 'Z' from replace('00000000','00000000','Z')),'YYYYMMDD') from dummy;
-- result: null
select to_date(trim(leading 'Z' from replace('20171120','00000000','Z')),'YYYYMMDD') from dummy;
-- result: 2017-11-20
But this looks like a dirty hack. Has anybody an idea for a more elegant solution?
DATS_TO_DATE does not return NULL if the given date is initial (0000-00-00), but a special date value (-1-12-31 to be precise).
To receive a NULL value in this case, as you requested, use the following statement:
NULLIF( DATS_TO_DATE(?), DATS_TO_DATE('00000000'))
e. g.:
INSERT INTO null_test VALUES (NULLIF( DATS_TO_DATE('00000000'), DATS_TO_DATE('00000000'))); => returns NULL
INSERT INTO null_test VALUES (NULLIF( DATS_TO_DATE('20171224'), DATS_TO_DATE('00000000'))); => returns 2017-12-24
As there are no tedious string operations involved, this statement should yield good performance.