Problem with CDS Scalar function for workday calculation

[felix_grab]

Hello,

I have built a CDS scalar function, with which I want to add workdays to a date using the SQL function "ADD_WORKDAYS".

Therefore I created a Scalar function with the following coding:

define scalar function /IFMSCM/ADD_WORKDAYS
  with parameters
    StartDate: abap.dats,
    NumberOfDays: abap.int4,
    FactoryCalendar: abap.char( 2 )
  returns abap.dats

After that I created the Implementation Reference and the corresponding class. The source code of the class contains the SQL function:

  METHOD execute BY DATABASE FUNCTION
                   FOR HDB
                   LANGUAGE SQLSCRIPT
                   OPTIONS READ-ONLY.

    result = to_dats(add_workdays (factorycalendar, startdate, numberofdays));

  ENDMETHOD.

As you can see in the source code, I wrapped the add_workdays function with a conversion to internal format, because it handed back the date in external (i.e. 2024-05-02). Normally I would expect to always work with the internal 8 character display (20240502) while I am not already on the UI level.

So I wrapped it with the to_dats function and handed the return parameter of the scalar function back as abap.dats.

Here is the integration into the CDS view

      /IFMSCM/ADD_WORKDAYS( StartDate => PReq.DowntimeFrom, NumberOfDays => cast( '1-' as abap.int4 ), FactoryCalendar => Plant.FactoryCalendar ) as ReschedulingDate,

This is working when I test the service, but it is displayed in the internal way:

So the next step was to try casting the field to abap.dats, with the following coding (on the higher CDS level):

      cast(case
        when ReschedulingDate >= $session.system_date then ReschedulingDate
        else '        '
      end as abap.dats )              as ReschedulingDate,

When I do this, the direct data preview of the CDS View looks good, with the correct date display, but the list report is no longer working, as soon as I select the data I get the following error messages:

Does anyone know, how I can achieve the external output on the UI, but can still use it in the CDS view as any other dats element?

Thanks and greetings

Felix

[junwu · Accepted solution]

else '        '

can you give some hardcoded value to see if it makes difference?

[felix_grab · Accepted solution]

Thank you very much, this was indeed the problem. I thought it occured because of the scalar function, but it seems that it was only the wrong initial value... sometimes you can't see the forest for the trees... Greetings and thanks Felix