ABAP News for Release 7.51 – Date and Time in ABAP CDS and Open SQL

When pushing down code from the application server to the database, of course you want to keep all the amenities that you have taken for granted when programming in ABAP on the application server. One of these amenities were the date and time handling capabilities that were available for the ABAP specific date and time types d and t together with the possibility of dealing with timestamps. All that handling of dates, times and timestamps, e.g. the possibility of adding numbers to date or time fields, is ABAP specific. Now, when you push down your code to the database you might suddenly miss some of ABAP’s possibilities. Therefore, the ABAP CDS and Open SQL teams together with the database interface teams work hard in order to provide you with as much of the former functionality as possible – and this in a platform independent way.

In release 7.50, ABAP CDS already offered a bunch of date functions and time functions that allow you to deal with dates, times and timestamps on database level. In Open SQL these functions haven’t been avaliable yet. Let’s see what’s in the box with release 7.51.

ABAP CDS

For 7.50 I was asked, when “a filter on HANA column store for SY-DATUM would be available“, and I said “Sorry, not in 7.50. Maybe 7.51 …“. And here we go. A new session variable $session.system_date can be used in ABAP CDS. When accessing a CDS entity with Open SQL that variable contains the same value as you would expect from the sytem field sy-datum in ABAP.

@AbapCatalog.sqlViewName: 'KELLERH_TST'
define view Kellerh_Test
  as select from
    tadir
    {
      pgmid,
      object,
      obj_name
    }
    where
      created_on = $session.system_date 

This CDS view retrieves all repository objects from table TADIR created today.

Furthermore, the set of date/time functions was enhanced with the following functions in ABAP CDS:

• TSTMP_TO_DATS
• TSTMP_TO_TIMS
• TSTMP_TO_DST
• DATS_TIMS_TO_TSTMP
• ABAP_SYSTEM_TIMEZONE
• ABAP_USER_TIMEZONE

The TSTMP_-functions carry out conversions of timestamps and the ABAP_-functions retrieve the sytem and user timezones of the current AS ABAP.

@AbapCatalog.sqlViewName: 'KELLERH_TST'
define view Kellerh_Test
  as select from
    demo_expressions
    {
      tstmp_to_dats( timestamp1,
                     abap_system_timezone( $session.client,'NULL' ),
                     $session.client,
                     'NULL' )      as dat,
      tstmp_to_tims( timestamp1,
                     abap_system_timezone( $session.client,'NULL' ),
                     $session.client,
                     'NULL' )      as tim
    } 

This CDS view reads the column timestamp1 of table demo_expression and converts it to a date and a time. The time zone used for this is the system time zone retrieved by abap_system_timezone for the current client taken from the session variable $session.client.

Open SQL

With release 7.51, also Open SQL starts to participates in the date/time game and offers its first date functions:

• DATS_IS_VALID
• DATS_DAYS_BETWEEN
• DATS_ADD_DAYS
• DATS_ADD_MONTHS

These functions have been available in ABAP CDS with release 7.50 already and they do the same in Open SQL, of course.

SELECT FROM dokhl
       FIELDS object,
              dokversion,
              dats_days_between( dokfdate, dokldate ) AS docu_age
       WHERE id = 'SD' AND
             object = 'ABAPCLASS' AND
             langu  = 'D'
       ORDER BY docu_age DESCENDING
       INTO TABLE @DATA(result).

This Open SQL SELECT calculates the age of the versions of a document of the ABAP Keyword documentation in its original language.

For more information see:

• Session variables in ABAP CDS
• Special functions in ABAP CDS
• Special functions in Open SQL