topic Re: Date conversion directly in CDS View entity in Application Development and Automation Discussions

Date conversion directly in CDS View entity

tafkap95 — Wed, 06 Nov 2024 02:15:46 GMT

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.

Re: Date conversion directly in CDS View entity

guido_s — Wed, 06 Nov 2024 06:14:55 GMT

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

Re: Date conversion directly in CDS View entity

RaymondGiuseppi — Wed, 06 Nov 2024 09:37:39 GMT

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.

Re: Date conversion directly in CDS View entity

tafkap95 — Wed, 06 Nov 2024 10:30:07 GMT

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.

Re: Date conversion directly in CDS View entity

RaymondGiuseppi — Wed, 06 Nov 2024 10:43:58 GMT

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

Re: Date conversion directly in CDS View entity

guido_s — Fri, 08 Nov 2024 08:13:53 GMT

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 }

Re: Date conversion directly in CDS View entity

tafkap95 — Wed, 06 Nov 2024 14:24:16 GMT

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.