Technical Articles
Some useful Functions used in ABAP CDS Views with Examples
Introduction
This Blog Post discusses about the approach of using some basic functions in day today requirements in CDS views. CDS Views are powerful views. If you are using SAP S/4HANA system then CDS is quite important for technical spoc.
I am writing this Blog Post for beginners, so that they can find many functions at one place. Also I am expecting that you know how to create a CDS view.
Now let’s discuss about the requirement and approach taken to achieve it.
Requirement
- Calculating Timestamp in CDS View from Date and Time
- Converting Time given in HH:MM:SS to Seconds
- Seconds between two date & time fields
- Adding Days in Date to make new Date
- Manipulating Date from YYYYMMDD to DD/MM/YYYY
- System Date function
- Showing Current system Timestamp
- Showing DD from Date in CDS
Approach
- Calculating Timestamp in CDS View from Date and Time: – The values of the columns ZDATE and ZTIME of the database table DEMO are combined into a time stamp by the conversion function DATS_TIMS_TO_TSTMP
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
ZDATE,
ZTIME,
DATS_TIMS_TO_TSTMP (ZDATE, ZTIME, abap_system_timezone($session.client,'NULL' ),
$session.client,'NULL') as ZTIMESTAMP
}
2. Converting Time given in HH:MM:SS to Seconds: – Through help of cast and substring function we will convert Time with HH:MM:SS to Seconds.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
ZTIME,
cast(
cast(cast(substring(ZTIME,1,2) as abap.numc( 2 )) as abap.dec( 15, 0 )) * 3600 +
cast(cast(substring(ZTIME,3,2) as abap.numc( 2 )) as abap.dec( 15, 0 )) * 60 +
cast(cast(substring(ZTIME,5,2) as abap.numc( 2 )) as abap.dec( 15, 0 ))
as abap.dec( 15, 0 ))as ZSECONDS
}
3. Seconds between two date & time fields: – The function TSTMP_SECONDS_BETWEEN calculates the difference between two specified time stamps. So in our case we will create time stamps in function TSTMP_SECONDS_BETWEEN.
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
ZDATE,
ZTIME,
ZDATE1,
ZTIME1,
tstmp_seconds_between
(
dats_tims_to_tstmp
(
ZDATE,
ZTIME,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL'
),
dats_tims_to_tstmp
(
ZDATE1,
ZTIME2,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL'
),
'NULL'
)as ZDIFF_SECONDS
}
4. Adding Days in Date to make new Date: – The function DATS_ADD_DAYS adds days to a specified date. As you see in Code below, +1 is no of days i am adding in Date. You can add as per your requirement.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
ZDATE,
dats_add_days(ZDATE, +1, 'NULL') as ZDATE_NEW
}
5. Manipulating Date from YYYYMMDD to DD/MM/YYYY: – Through help of cast and concat function we will create Date in format DD/MM/YYYY.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
Date_column,
Cast(
Concat(
Concat(
Concat(substring(Date_Column, 5, 2), ‘/’),
Concat(substring(Date_Column, 7, 2), ‘/’),
),
Substring(Date_column, 1, 4)
)
As char10 preserving type) as ZCONVERTED_DATE
}
6. System Date Function: – The session variable $session. system_date is used in a CDS view to provide direct access to the current system date. Using this function it will give you system date.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
$session.system_date as systemdate
}
7. Showing Current system Timestamp: – Function tstmp_current_utctimestamp() is used for showing system timestamp.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
tstmp_current_utctimestamp()
}
8. Showing DD from Date in CDS: – In this example we will make new column ZDD from Date.
Example:-
@AbapCatalog.sqlViewName: 'DEMO_CDS_DATTYM'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'DEMO'
@VDM.viewType: #BASIC
@Analytics.dataCategory: #FACT
@Analytics.dataExtraction.enabled: true
Define view demo_cds_date_time
as select from demo
{
ZDATE,
cast((substring(ZDATE, 7, 2)) as abap.char(2)) as ZDD
}
Conclusion
Using this blog post, It will be easy for developers to understand these functions and how to use them in different requirements they face. As a Technical Expert in S/4HANA it is important to know CDS Views.
Note:
- I am the owner of all the content and code used in this blog post.
Hello,
Thanks for the blog.
regarding "YYYYMMDD to DD/MM/YYYY",
Hello Masaaki Arai,
Thanks for your valuable Suggestion. It will be great help for others.
Regards
Lalit Yadav
Thank you! This is covered at length in the documentation but there isn't a single example of the actual command, so this is very useful.
There is also a handy cheat sheet but it doesn't have the full command examples and points to the documentation, which (see above) doesn't have any examples either. ¯\_(ツ)_/¯