How to calculate the interval between two timestamps in CDS view

Timestamp in ABAP transparent table. Data type is dec:

There is a requirement: calculate the number of days between the two timestamps and discard the hours after the 8 digits of the timestamp year-month-day: minutes: seconds.

For example: if the timestamp is 20180918173132, discard 173132, keep only 20180918, and then calculate the day interval.

It is not possible to directly use CDS view's string manipulation function substring because the timestamp type dec does not match the string type expected by substring.

Solution:

First cast the timestamp field type from dec to abap.dats:

@ AbapCatalog.sqlViewName: 'zproday'@AbapCatalog.compiler.compareFilter: true@AccessControl.authorizationCheck: # CHECK@EndUserText.label:' Day between'define view zdate_day_between as select from comm_product {key comm_product.product_id as prod_id,comm_product.product_guid as prod_guid,comm_product.valid_from as valid_from,comm_product.valid_to as valid_to,cast (substring (cast (valid_from as abap.char (32)), 1mai 8) as abap.dats) as from_date Cast (substring (cast (valid_to as abap.char (32)), 1pc8) as abap.dats) as to_date}

Then use the CDS view standard time processing function DATS_DAYS_BETWEEN:

@ AbapCatalog.sqlViewName: 'zdbetw'@AbapCatalog.compiler.compareFilter: true@AccessControl.authorizationCheck: # CHECK@EndUserText.label:' Day between'define view zc_date_day_between as select from zdate_day_between as host {key host.prod_guid,host.prod_id,host.from_date,host.to_date,DATS_DAYS_BETWEEN (host.from_date,host.to_date) as no_of_days}

Test results:

For more original Jerry articles, please follow the official account "Wang Zixi":