2

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?

Suncatcher
  • 10,355
  • 10
  • 52
  • 90
Psio
  • 83
  • 2
  • 5

2 Answers2

3

As explained in my presentation Innovation with SAP HANA - What are my options all that string manipulation is really not necessary.

Instead, use the appropriate conversion functions when dealing with ABAP date and time data. In this case, DATS_TO_DATE is the correct function.

with in_dates as
(            select '20171120' as in_date from dummy
  union all  select '00000000' as in_date from dummy)

select 
        dats_to_date(in_date)
      , in_date 
from in_dates;


|DATS_TO_DATE(IN_DATE)   |IN_DATE 
-------------------------+---------
|2017-11-20              |20171120
|?                       |00000000

The ? here is the output representation for NULL.

Lars Br.
  • 9,949
  • 2
  • 15
  • 29
1

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.

TheSpecter
  • 26
  • 1
  • In Eclipse, the internal value '-1-12-31' is displayed as '?', identical to null. Only with "select ... where field is null" you can see the difference. You are right, DATS_TO_DATE returns not a null. – Psio Nov 23 '17 at 10:05
  • Good point! The conversion to a NULL value indeed happens when the special date value is transferred to the client (JDBC in this case). The client software then maps to a valid java date type, which is a `null` valued date. – Lars Br. Nov 29 '17 at 10:24
  • It's probably good to note that using the special "INITIAL" date allows avoiding NULL values in that column. For column store tables there is no increased space requirement for this INITIAL date, but SQL statements can now operate without having to consider additional logic to cater for a potential NULL values. So, you might want to keep this value after all. – Lars Br. Nov 30 '17 at 01:28