Skip to Content
Author's profile photo Horst Keller

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:

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sunil Sankar
      Sunil Sankar

      I feel we need a system fields available in LHS of Open SQl, if we intended to achieve code push. For an eg - considering on month, derive a value / calculate value from DB.

      Found a cheat though..

      DELETE FROM demo_expressions.
      INSERT demo_expressions FROM @( VALUE #( id = '1' ) ).

      SELECT  CASE substring( a~datum,5,2 )
      WHEN '01' THEN Jan

      WHEN '02' Then 'Feb'

      FROM demo_cds_system_fields  AS a

      JOIN Table_B AS b ON 1 = 1 INTO @DATA(Month).

      Do we have any other option which i am not aware of or anything planned for future release?

       

       

       

       

       

       

      Author's profile photo Sunil Sankar
      Sunil Sankar

      Is there any other option in Open sql to to return a value based on current month??

      I used demo_cds_system_fields but feel it is not right option.

      SELECT  CASE substring( a~datum,5,2 )
      WHEN ’01’ THEN Jan

      WHEN ’02’ Then ‘Feb’

      FROM demo_cds_system_fields  AS a

      INTO @DATA(Month).

      Author's profile photo Jay Malla
      Jay Malla

      Hi Horst,

      Is there an easy way to get the day of the week in the CDS view?  I can get the system date and I can convert it but I can’t find any documentation on how to get the day of the week.  I need this and then I need to calculate the Monday of that week and the Sunday of the week.  This is easy in ABAP but I can’t find a way in CDS.

      I looked through ABAP CDS – Date Functions

       

      Basically, I am trying to do something like this with the system date or a parameter that takes in a date:

      SELECT  DAYNAME ('2017-09-04') "DayName" FROM DUMMY;

      Result:

       MONDAY

       

      ‘Thanks,

      Jay

      Author's profile photo Leon van Niekerk
      Leon van Niekerk

      Maybe table SCAL_TT_DATE can help you

      Author's profile photo Vidar Gran Terjesen
      Vidar Gran Terjesen

      It is handy with built in variables, would it also possible to use #SYSTEM_DATE and $session.system_date  as  a substring say to an annotation?

      Say you want to create a report for the current fiscal year and previous month as default, so you add year and period as default filters below hardcoded period 2 of 2020:

      @Consumption.filter.defaultValue: '2020'
      @EndUserText.label: 'Fiscal year'
      @UI.selectionField.position: 1

      gjahr,

      @Consumption.filter.defaultValue: '002'
      @EndUserText.label: 'Period'
      @UI.selectionField.position: 2

      poper,-_

      But what you really want to do is to have the current year, and current period.

      In theory something like @Consumption.filter.defaultValue: #SYSTEM_DATE(4) or substring(session.system_date,4,4)