Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Date conversion directly in CDS View entity

tafkap95
Participant
0 Kudos
522

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.

6 REPLIES 6

guido_s
Participant
446

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

0 Kudos
396

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.

0 Kudos
380

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
}

 

 

0 Kudos
324

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
Active Contributor
407

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

Or post the code you've already tried.

389

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