CDS View - Get Year, Quarter, Month, Week, from Date

[keega]

Hi all!

I'm working on a custom GR forecast report in CDS and have run into a little obstacle where I need to derive the following:

I have the delivery date, but not sure where to begin extracting the required data.

I'll continue to look for examples, but can someone give a nudge in the right direction, please?

Thank you in advance.

[Waruna]

Hi,

You could get year, month and week by implementing table function and AMDP class as following. Assumed base table as EBAN.

Table Function under Core Data Services:

Table Function Code:

@EndUserText.label: 'TF for extract day year from date'
define table function ZCDS_TF_DATE
  //with parameters parameter_name : parameter_type
returns
{
  clnt   : abap.clnt;
  banfn  : banfn;
  bnfpo  : bnfpo;
  lfdat  : eindt;
  ryear  : abap.numc( 4);
  rmonth : abap.numc( 2 );
  rweek  : abap.numc( 4 );
}
implemented by method
  zcds_cl_date=>get_year_month;<br>

Then needs to implement new abap class zcds_cl_date and method get_year_month given in above table function. class name and method name can be given as your own.

SQL statement with year(), month() and week() functions can be used to extract year, month and week from given date field as implemented under following function get_year_month. Column names with return SQL statement must be same as define in the returns list in above table function.

CLASS zcds_cl_date DEFINITION PUBLIC .

  PUBLIC SECTION.
    INTERFACES if_amdp_marker_hdb.

    CLASS-METHODS get_year_month FOR TABLE FUNCTION ZCDS_TF_DATE.

  PROTECTED SECTION.
  PRIVATE SECTION.
ENDCLASS.



CLASS zcds_cl_date IMPLEMENTATION.

METHOD get_year_month by DATABASE FUNCTION FOR HDB LANGUAGE
                            SQLSCRIPT OPTIONS READ-ONLY using eban.

return select mandt as clnt,
            banfn,
            bnfpo,
            lfdat,
            year( lfdat ) as ryear,
            month( lfdat ) as rmonth,
            week( lfdat ) as rweek
        from eban;


ENDMETHOD.

ENDCLASS.

After activated the above class table function ZCDS_TF_DATE can be used to generate the required result and can be used same as the standard table or view with another CDS views.

Output with table function ZCDS_TF_DATE will be generated as following.

Table function can be used with CDS view as following.

@AbapCatalog.sqlViewName: 'ZCDS_DATE_FUNC'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Day Month Year Extraction'
define view ZCDS_DATE_FUNCTIONS as select from ZCDS_TF_DATE
{
    banfn,
    bnfpo,
    lfdat,
    ryear,
    rmonth,
    rweek
}

[keega]

This too, looks like a likely candidate. I will try this as well. Thank you Waruna, I'll check back in a bit.

[Sandra_Rossi]

I guess year, month and quarter are fine, you are only asking about week, right?

Week is more complex because the algorithm may depend on country or whatever. Will the week starting day be Sunday or Monday, is January 1st part of last week of previous year, or always be week number 1. Etc.

NB: CDS string functions -> ABAP Keyword Documentation (sap.com)

also CASE and WHEN are needed for quarter (ABAP Keyword Documentation (sap.com)).

LEFT( DeliveryDate, 4 ) AS DeliveryDateYear,
SUBSTRING( DeliveryDate, 5, 2 ) AS DeliveryDateMonth,
CASE WHEN SUBSTRING( DeliveryDate, 5, 2 ) between '01' and '03' THEN '1'
     WHEN ... ENDCASE AS DeliveryDateQuarter,
???? as DeliveryDateWeek,

[keega]

Ah, great point being country-dependent. Let me play around with it today, and see how it comes out.

Thanks so much.

[keega]

Sorry, I meant to answer your question: I was asking about all 4, not just the week.

[keega]

Hi,

I couldn't use AMDP in this particular query, and ran into a few obstacles with using substrings, BUT, I did join I_CalendarDate:

inner join   I_CalendarDate as Date on likp.erdat = Date.CalendarDate

Date.CalendarYear    as DelDateYear,
Date.CalendarQuarter as DelDateQtr,
Date.CalendarMonth   as DelDateMonth,
Date.CalendarWeek    as DelDateWeek,

and got this output:

Not sure if this is the best way, but it produced what I was looking for.

Thank you both for you time and input.