Standard service - a fixed amount of work that is performed at a fixed price.  Post Service

  

Tuesday, 05 February 2019 03:48

ABAP for SAP HANA. Part XXVII. Usage of Built-In Functions in CDS Part – III

Written by 
Rate this item
(0 votes)

Not too long ago we fell short of discussing Date and Time functions in CDS View. If you had missed, no worries take a look at Built-In Functions

– CDS View Part II.

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.

1. ADD DAYS
2. ADD MONTHS
3. DAYS BETWEEN TWO DATES
4. 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)

Also Read : What’s there in the 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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<code>@AbapCatalog.sqlViewName:'ZDT_TIME_FN_V'
@AbapCatalog.compiler.compareFilter:true
@AbapCatalog.preserveKey:true
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label:'Test Date and Time'
defineviewZDT_TIME_FN
withparametersp_add_days:abap.int4,
p_add_months:abap.int4,
@Environment.systemField:#SYSTEM_DATE
p_curr_date:abap.dats
asselectfromsflightasa{
keya.carridasFlgId,
keya.connidasFlgConnId,
keya.fldateasFlgDate,
dats_add_days(a.fldate,:p_add_days,'INITIAL')asAdded_DT,
dats_add_months(a.fldate,:p_add_months,'NULL')asAdded_MT,
dats_days_between(a.fldate,$parameters.p_curr_date)asDays_BTW,
dats_is_valid(a.fldate)asIs_Valid
}</code>
Observe the declaration of the system field and it’s usage.

Food for Thought –

What do you think would be the output of below code snippet?

1
2
3
4
5
6
7
8
<code>DATA:v_stringTYPEchar10,
v_numTYPEnum10.
v_string='12wer34op'.
v_num=v_string.
WRITE:v_num.</code>

To find the answer, check this. Maths in ABAP

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:

InputDate consideredExpectedOutputObservation
-7357402015-05-200001010100000000BUG
-7357402015-05-2100010102 00010102As 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. Comment and Share.

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<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'
defineviewZDT_DT_TIME_FN
withparameters@Environment.systemField:#SYSTEM_DATE
p_curr_date:abap.dats
asselectfromsflconnasa{
keya.agencynumasFlgAgy,
keya.flconnasFlgCon,
a.arrtimeasFlgArr,
//ConvertArrivalTime
dats_tims_to_tstmp
($parameters.p_curr_date,
a.arrtime,
abap_system_timezone($session.client,'NULL'),
$session.client,
'NULL')asFlg_Arr_Conv,
abap_system_timezone($session.client,'NULL')
asMyTimeZone
}</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.

With that we come to an end of another article. Hope as promised earlier your fate and mine would change.

Please Follow, Share, Comment, Ask, Challenge.

We have a very active Telegram (App) SAP Technical Group with more than 3750+ SAP Technical Practitioners from 6 Continents of the SAP World. Please join it using below link.
Telegram SAP Technical Discuss Group. You need to install the Telegram App first on your mobile device. Once you have it on your mobile, you can join the group and also access it from the Web on your computer and laptop.

Free SAP ABAP for HANA Training Tutorials Exercises.

Read 120 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.