Skip to Content
Technical Articles
Author's profile photo Michael Keller

Calculate the age of your data with DATS_DAYS_BETWEEN

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“.

 

Assigned Tags

      7 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Suhas Saha
      Suhas Saha

      For a moment i thought you were gonna talk about the “data aging” business function.

      Your blog explains the usage of dats_days_between SQL function. ? May i suggest that you change the title of the blog? 

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Sure, done! 🙂

      Author's profile photo Justin Loranger
      Justin Loranger

      Here is how I have worked around a similar requirement.  It may not be the best, but it works.

      @AbapCatalog.sqlViewName: 'ZTESTDATAAGE'
      @AbapCatalog.compiler.compareFilter: true
      @AbapCatalog.preserveKey: true
      @AccessControl.authorizationCheck: #NOT_REQUIRED
      @EndUserText.label: 'Data Age'
      define view ztest_data_age_example
        with parameters
          @Environment.systemField : #SYSTEM_DATE
          p_datum : syst_datum
        as select from zage_example
      {
        client,
        example_no,
        birthday,
        dats_days_between( birthday, :p_datum )          as age_in_days,
        div(dats_days_between( birthday, :p_datum ),365) as age_in_years
      }

      When you test execute, you will be prompted for a date, but it can be left blank since it will default to the current system date.

      Produces the following output…

       

      Using in program code…

      REPORT ztest_age_example.
      
      PARAMETERS p_days TYPE i DEFAULT '1000'.
      
      SELECT * FROM ztest_data_age_example
      WHERE age_in_days >= @p_days
      INTO TABLE @DATA(agetab).
      
      cl_demo_output=>display( agetab ).

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      Thanks for your help. So it looks like DATS_DAYS_BETWEEN really can't work with the session variable. I thought I made some mistake 😉

      Author's profile photo Lars Breddemann
      Lars Breddemann

      I think note

      2699516 - Unable to use $session.system_date with calculated field and function DATS_DAYS_BETWEEN

      explains what's happening here.

      The "solution" is essentially the same as what Justin showed: making sure that both data types for the DATS_DAYS_BETWEEN function parameters are DATS.

      Apart from that: this number_of_days/356 = number_in_years formula is an approximation as it doesn't cover leap years. You want to be careful when using this to send out birthday cards ... 🙂

       

       

      Author's profile photo Michael Keller
      Michael Keller
      Blog Post Author

      If I remember correctly, Jelena Perfiljeva  recently received birthday greetings on the wrong day? But I could be wrong, too 🙂 Anyway, thanks for the clarification.

      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      You are correct. 🙂