SAP HANA CDS view – How to get Year and Month from a date field

[Shravan_]

This blog post will give an example of using SAP HANA Core data services (CDS) function as SUBSTRING, CAST and CONCAT for HANA view. This blog post will help a beginner who working on CDS view and date or any other SAP fields type.

Brief requirement: I was working on a BI/BW requirement to create new CDS/HANA view. There was a requirement to display Year (YYYY), Year and Month (YYYYMM) field for which no SAP table fields ‘Year’ available so the requirement to convert current date fields (DATS).

CDS has numerous built-in function available with the help of them we can create new data type and output field in view. I have used Substring, Cast, Concat to get new fields in view which will be consumed for BW reporting purpose e.g. Year and Month for output.

I have used “SFLIGHT” table in sample code.

/* Author: Shravankumar Tiwari */

@AbapCatalog.sqlViewName: ‘ZDDS_YEAR_MONTH’

@AbapCatalog.compiler.compareFilter: true

@AbapCatalog.preserveKey: true

@AccessControl.authorizationCheck: #CHECK

@EndUserText.label: ‘CDS View – Date to Year, Month’

define view ZDD_YEAR_MONTH as select from sflight {

sflight.mandt,

sflight.carrid,

sflight.connid,

sflight.fldate,

sflight.price,

//Now we will display Year, Year Month from the date field

@Consumption.labelElement: ‘Flight Year_Char’ SUBSTRING (sflight.fldate,1,4) as Date_Year_char,

@Consumption.labelElement: ‘Flight Year_Numc’ cast(SUBSTRING (sflight.fldate,1,4) as abap.numc(4)) as Date_Year_NUMC,

CONCAT ( SUBSTRING (sflight.fldate,1,4), SUBSTRING (sflight.fldate,5,2) ) as Date_YearMonth //YearMonth(YYYYMM)

}

We get CDS output as shown below:



 

DDL SQL View with data types:



   

As we have come to the end of this blog post I hope with the above example you can create new data type in CDS view (Character or Numeric data types) with more ease. Please refer SAP document for more built-in function for CDS.

 

Thank you for reading and learning.

Please refer SAP document for more information:

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abensql_functions_string.htm

https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-US/abencds_f1_cast_expression.htm