Not too long ago we fell short of discussing Date and Time functions in CDS View.
Please bear with my style of presentation. I do not believe in the orthodox text book flow. Isn’t it kind of boring? So, I try to be funny, add lots of emojis in the so called serious SAP blog and try to refresh your jammed brain. After all, learning serious stuffs can be fun too.
We have extra spice this time. Special Segment. Don’t miss it !!!
The new story goes this way. We have 3 important things to deal when DATE is talked about. DAY, MONTH and YEAR. We are limiting our discussion to first two in this article. Let’s get started.
- ADD DAYS
- ADD MONTHS
- DAYS BETWEEN TWO DATES
- DATE VALIDATION
1. ADD DAYS:
Do you recall? There are some numbers on either side of the great “0” on number line, called as INTEGERS. That’s the hint to define ADD_DAYS in CDS Views.
Yes, absolutely. ADD_DAYS can be used not just for adding but also SUBTRACTING.
Thumb rule of ADD_DAYS function is, one needs to pass:
i. First Parameter : Date to which days to be added
ii. Second Parameter : Number of Days to be added
iii. Third Parameter : Error Handling- Four values can be :
- “FAIL” (an error raises an exception)
- “NULL” (an error returns the null value)
- “INITIAL” (an error returns the initial value)
- “UNCHANGED” (an error returns the unmodified value of date)
2. ADD MONTHS
Straight forward with three parameters. One each for input date, number of months to be added and error handling respectively.
Names can be misleading . One can even subtract.
3. DAYS BETWEEN TWO DATES
Don’t be smart here. Never calculate the days between two DATES. It can ruin your day.
Back to CDS, two inputs, one for first extreme and other for other extreme.
4. DATE VALIDATION
What more can you ask, this can be used to validate the date and know whether your DATE was valid ( if not, just WISH to have another ) or NOT.
Output returns either “1” or “0”.
For all the above functions ONE Code Example is used. But to provide an enthralling and excruciating experience, we have MORE than ONE Test Case.
CODE SNIPPET:
<code>@AbapCatalog.sqlViewName: 'ZDT_TIME_FN_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test Date and Time'
define view ZDT_TIME_FN
with parameters p_add_days : abap.int4,
p_add_months: abap.int4,
@Environment.systemField: #SYSTEM_DATE
p_curr_date : abap.dats
as select from sflight as a {
key a.carrid as FlgId,
key a.connid as FlgConnId,
key a.fldate as FlgDate,
dats_add_days (a.fldate, :p_add_days , 'INITIAL') as Added_DT,
dats_add_months (a.fldate, :p_add_months, 'NULL' ) as Added_MT,
dats_days_between (a.fldate, $parameters.p_curr_date ) as Days_BTW,
dats_is_valid (a.fldate) as Is_Valid
} </code>
Observe the declaration of the system field and it’s usage.
<code>DATA: v_string TYPE char10,
v_num TYPE num10.
v_string = '12wer34op'.
v_num = v_string.
WRITE: v_num.</code>TEST CASE 1 (only look for P_ADD_DAYS):
Very IMPORTANT : Observe that parameter “P_CURR_DATE” does not have an asterisk. Means that it is not a mandatory to provide . Then we have two ways to deal. One leave blank and other to forcefully provide a date. Second case has been dealt in next example.
Wondering why the “-735740” was given in P_ADD_DAYS …..There is something called as BVA in Software Testing…Go and check it out for yourself what does that mean.
Now then….Let it be loud and clear there is a BUUUGGG !!! Second consecutive time we are proud to point out that SAP cannot be foolproof. Look closely. We should have ideally got INVALID.
Now, let’s analyse what we have done:
| Input | Date considered | Expected | Output | Observation |
| -735740 | 2015-05-20 | 00010101 | 00000000 | BUG |
| -735740 | 2015-05-21 | 00010102 | 00010102 | As per expectation |
Question Remains : Where is 1st January of Year 0001 ??? Anyone can find it out bring it to us and let the world know.
On 20th May 2015, it was 735740 calendar days. If 01/01/0001 + 735740 = 20/05/2015, then 20/05/2015 – 735740 should be 01/01/0001. But the output is showing 00/00/0000. Why? Any explanation?
TEST CASE 2:
6 Days were added to 7th Oct and result was 13th Oct. – TICK
Days between flight date 7th Oct and execution date, 04th Feb 2019 – TICK
Is the flight date 07th Oct 2015 valid – VALID – TICK
BONUS SEGMENT:
(the special segment which we promised above) .. disappointed?
In continuation with the previous topic, in this section we will explore two more functions.
5. DATS_TIMS_TO_TSTMP
Huge list of parameters (just 5) needs to be passed in order to combine date and time AND also convert to different timezone.
First input is date, second is time, third timezone, fourth client and finally error handling.
6. TIMEZONE
Straight as an arrow, just pass the session client and get system’s Time Zone.
Note: It is very important to pass Client while working in HANA.
TEST CASE 3:
<code>@AbapCatalog.sqlViewName: 'ZDT_DT_TIME_FN_V'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Test Date and Time 2'
define view ZDT_DT_TIME_FN
with parameters @Environment.systemField: #SYSTEM_DATE
p_curr_date : abap.dats
as select from sflconn as a{
key a.agencynum as FlgAgy,
key a.flconn as FlgCon,
a.arrtime as FlgArr,
//Convert Arrival Time
dats_tims_to_tstmp
($parameters.p_curr_date,
a.arrtime,
abap_system_timezone( $session.client,'NULL' ),
$session.client,
'NULL') as Flg_Arr_Conv,
abap_system_timezone( $session.client,'NULL' )
as MyTimeZone
}</code>
There are just TOO many things to check out. Start with dats_tims_to_tstmp parameters.
As usual we need test and validate. It has become a custom which can be broken, but I will refrain.
Timezone of the system is CET. But, I am in a different part of the world
VALIDATION:
Time was picked from the database table. However, date was system date.
Again as is the ritual, Google introduced me (tech savvy) to Time Savvy.
