Date conversion directly in CDS View entity

[tafkap95]

Hello everyone,

I want to change the value of a Date field directly in my CDS View; I want to convert this value to the last day of the previous month. It seems very simple as a need but in CDS View I have trouble 🙂

I tried to use the functions dats_add_months, dats_add_days, combined with cast and substring but I always run into compilation problems, in short I can't do it, in pure ABAP it would take a few seconds 🙂

I was suggested to possibly go through an AMDP function, what do you think?

For example if I have the value 20241015, the converted value will be 20240930, if I have the value 20240123 the converted value will be 20231231.

Thank you very much for your help.

[guido_s]

Hi tafkap (prince fan?),

it would be helpful to know what code you exactly tried to use and which error you received.

Have you seen the post CDS View: Add days to Date? Here's explained how to use DATS_ADD_DAYS(date,days,on_error) and how to convert the parameter values.

Guido

[tafkap95]

Hi Guido, yes Prince fan, well seen, what culture 😊

I have seen the Date functions (DATS_ADD_DAYS, DATS_ADD_MONTHS...) but how can I use them in my context?

If I take the date 10/10/2024 the conversion should give 09/30/2024 as a result, very good but how can I do this in a CDS. I know that to get this result I would have to subtract 10 days from my date 10/10/2024 but how can I calculate these 10 days, save this variable and apply it to DATS_ADD_DAYS(myDate,-10,'NULL')

It seems difficult to me to do operations individually by day/month/year because the problem of changing the year and/or leap year arises.

[guido_s]

Hi tafkap,

to calculate the last day of the previous month you can try this (aedat is the given date):

DATS_ADD_DAYS( cast( concat( SUBSTRING (aedat,1,6), '01' ) as abap.dats ),-1,'NULL') as last_day_prev_month

 Full example:

@AbapCatalog.sqlViewName: 'ZFI_V_TST'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Test'
define view ZFI_CV_TST as select from prps {
  aedat,
  DATS_ADD_DAYS( cast( concat( SUBSTRING (aedat,1,6), '01' ) as abap.dats ),-1,'NULL') as last_day_prev_month
}

[tafkap95]

Hello Guido, it works perfectly, a huge thank you for your help 🤗

Your idea is perfect: you position yourself on the 1st day of the date then you apply -1 day.

[raymond_giuseppi]

So you need to subtract the day value from the date, look at following built in functions

• EXTRACT_DAY( date|utclong )
• ADD_DAYS( {date|utclong},days )

Or post the code you've already tried.

[raymond_giuseppi]

With date 10/10/2024 (MM/DD/YYYY) EXTRACT_DAY will return 10, the ADD_DAYS with days = minus 10 will return 09/30/2024