What’s there in the Date?

1) Ever wondered, why SAP saves date in YYYYMMDD format?

First why not DDMMYYYY or MMDDYYYY?

Lets assume SAP saved date as DDMMYYYY.
1st Jan 2000 is saved as 01012000
1st Dec 2000 is saved as 01122000
But look how 1st Dec 1999 looks like: 01121999.

Here is the problem.. Now 1st Jan 2000 become less than 1st Dec 1999.
Numerically, 01012000 < 1121999. But; 1 Jan 2000 should be greater than 1st Dec 1999.
So, DDMMYYY is not a good way to save date.

Now, lets assume SAP saved date as MMDDYYYY.

1st Jan 2000 is saved as 01012000
1st Dec 2000 is saved as 12012000
But look how 1st Dec 1999 looks like: 12011999.

Here is the problem again. 1st Jan 2000 become less than 1st Dec 1999.

Numerically, 01012000 < 12011999. But; 1 Jan 2000 should be greater than 1st Dec 1999.
So, MMDDYYY is also not a good way to save date.

Our interpretation: Why YYYYMMDD is the way to save?

1st Jan 2000 is saved as 20000101
31st Dec 2000 is saved as 20001201
1st Dec 1999 is saved as 19991201

Numerically 19991201 < 20000101. And also theoretically, 1st Dec 1999 is less than 1st Jan 2000.

Seems, SAP is playing with numbers. Future dates are always greater than present dates and past dates. And this can be achieved only if you save date as YYYYMMDD. Isn’t it smart thinking by the system.. 🙂

2) Our young ABAPers often get confused, how to use a date and time together in WHERE clause during SELECTs.

Say the requirement is to fetch all Sales Order data from VBAK which were create after 7 AM on 1st Jan 2014 till 11 AM on 9th July 2014.

The first intuition of the developer is to write:

If the developers does not do the unit test properly, then (s)he would not realize the data loss. The above query is logically incorrect.

What is the issue?
It would fetch only those sales order which were created between 7 AM to 11 AM every day from 1st Jan to 9th Jan. Any sales order created after 11 AM till 7 AM would not be selected. For 2nd Jan onward, 24 hours data should be selected i.e anything before 7 AM and also after 11 AM.

What would the correct SELECT fetch?
Only for 1st Jan, data before 7 AM should not be selected.
Only for 9th July, data after 11 AM should not be selected. For rest all days in between, all 24 hours data should be selected.

So the right approach would be:

PS: You can prevent the DELETE statements by using the ERZET properly in the WHERE clause of the SELECT. But, however you frame your WHERE clause, it should do the same thing as done by DELETE statements above.

3) Confusion while using Validity Period?

Say your requirement is to fetch data from a table (A017) in the validity range of 1st Jan 2014 till 31st Dec 2014.

Does this mean, you need to frame your query WHERE clause as:

Simple answer is .. a big NO.

Say, an entry is there in A017 which has valid from date as 1st Jan 2000 and valid to date as 30th June 2014. This entry is valid in the range 1st Jan 2014 to 31st Dec 2014. But, the above SELECT would not fetch this and there would be data loss.

Right way is: Criss-Cross of Validity From and To in the WHERE clause.

This WHERE clause would pick the row mentioed above in A017. Valid from 1st Jan 2000 is LE ‘20141231’ and the Valid To 30th Jun 2014 is GE ‘20140101’.

Does it make sense? Remember the Criss-Cross logic.. 🙂

4) Change date to internal and external format.

This is a very common requirement and developers tend to split/concatenate the date values and try to change it to desired internal/external format.
Say date is 01/09/2014.. Developers try to split at ‘/’ and format it to 20140109 to get the internal format.

At times, developer tend to hard code ‘/’ or ‘.’ to get the external format of the date. They do not realize that, the end users or business users may not have the
same date format as the developer’s. In those scenarios, hard coding would lead to issue.

In order to prevent it, we should use SAP provided function modules viz. CONVERT_DATE_TO_EXTERNAL and CONVERT_DATE_TO_INTERNAL.

 

 

Image source: www.elegantthemes.com

Share on Facebook11Share on LinkedIn0Tweet about this on TwitterShare on Google+3Email this to someonePin on Pinterest0Share on Reddit0
Isn't it Fair to Share??