How to calculate the number of days between two timestamps in a CDs view

The time stamp of the ABAP transparent table, the data type is Dec:

There is a requirement: Calculate the number of days between these two timestamps, discard the timestamp year-month-day 8 digits after the hour: minutes: seconds.

For example: If the timestamp is 20180918173132, discard 173132, leave only 20180918, and then calculate the days interval.

It is not possible to use the string manipulation function substring directly with the CDs view, because the timestamp type dec and substring do not match the expected string type.

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)),1,8) as abap.dats) as from_date,cast(substring(cast(valid_to as abap.char(32)),1,8) 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:

To get more original Jerry's articles, please follow the public number "Wang Zixi":

How to calculate the number of days between two timestamps in a CDs view