Calculate the age of your data with DATS_DAYS_BETWEEN

[Michael_Keller]

Dear community, data also have an age 🙂 And age plays sometimes a role in different business processes.

For example, a user is interested which purchase orders were created in the last week. You can then use the BEDAT field in the EKKO database table to select all data records with the help of a period. But that is for me more the answer to the question of which records were born/created in the last week. So you talk about their "birthday" and select/sort accordingly.

However, the age of data in years can also be determined. Not that this is really necessary. It's more a fun thing. So far, for me the "birthday" of records was enough to solve my business requirements. At least as far as I can remember 😉

But as I've already written, this is more about fun than seriousness. You can also easily calculate the age of your data. So you get an extra information that can be used for example in an ALV. Perhaps one of your users will be happy with that kind of information.

Here's a little example database table.

@EndUserText.label : 'age of data example'

@AbapCatalog.enhancementCategory : #NOT_EXTENSIBLE

@AbapCatalog.tableCategory : #TRANSPARENT

@AbapCatalog.deliveryClass : #A

@AbapCatalog.dataMaintenance : #ALLOWED

define table zage_example {

  key client     : abap.clnt not null;

  key example_no : numc2 not null;

  key birthday   : dats not null;



}

Actually with one record (EXAMPLE_NO: 01, BIRTHDAY: 01.04.1972).

Ok, here's a little demo class to test the calculation via Open SQL. I've used the date function DATS_DAYS_BETWEEN and the numeric function DIV to calculate.

CLASS zcl_age_example DEFINITION

                      PUBLIC

                      FINAL

                      CREATE PUBLIC.



  PUBLIC SECTION.

    INTERFACES if_oo_adt_classrun.



  PROTECTED SECTION.



  PRIVATE SECTION.

ENDCLASS.



CLASS zcl_age_example IMPLEMENTATION.

  METHOD if_oo_adt_classrun~main.

    SELECT example_no,

           birthday,

           dats_days_between( birthday, @sy-datum ) AS age_in_days,

           div( dats_days_between( birthday, @sy-datum ), 365 ) AS age_in_years

           FROM zage_example

           INTO TABLE @DATA(result).



    out->write( result ).

  ENDMETHOD.

ENDCLASS.

As output is redirected to my Eclipse Console, we can run the class by F9. Output in ABAP Console looks like that. Looks good so far.

I've tried to do the same calculation with ABAP CDS. But it didn't work. Session variable $SESSION.SYSTEM_DATE can not be used as I want to? Can an ABAP CDS expert tell me the solution? Thanks in advance.

@AbapCatalog.sqlViewName: 'ZAGEOFDATA'

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: 'age of data example'

define view AGE_EXAMPLE

  as select from zage_example

{

  client,

  example_no,

  birthday,

  dats_days_between(birthday,$session.system_date) as age_in_days,  

  div(dats_days_between(birthday,$session.system_date),365) as age_in_years  

}

 

Best regards, thanks for reading and please stay healthy

Michael

 

P.S.: Please support the virtual wishing well.

P.S.S.: Not tired of reading blogs? Read about the "standalone editing of ABAP in VS Code".