I spent some considerable time investigating for any standard option to convert the date from JD Edwards (JDE) standard "CYYDDD" date format to the humanly recognizable Gregorian Date, but there seems to be no known function available out of the box.
For those who are new to the concept of JD Edwards date (like me), here is a brief intro.
In JDE, the format for Date is C-YY-DDD. (C for Century, YY for the two digit year and DDD for a number between 1 to 365 (or 366 for leap year)) Since JDE was invented in the 20th century (obviously), I believe they started this format which supports for only dates starting year 1900, (as I understand, JDE does not support dates before the year 1900).
Here are a few examples of JDE dates and their equivalent Gregorian date.
115141 - May 21, 2015
102317 - November 13, 2002
99054 - February 23, 1999
So basically if the JDE date is 5 digit, C in the CYYDDD is 0 (zero), which indicates that the date is in the 20th century (i.e. starting year 1900 and before year 2000), and if it starts with 1 (C in CYYDDD – century), then it is in the 21st century starting year 2000.
It looks like a difficult conversion at the first look, but in fact, it is simple on a little bit of digging and the date function of Microsoft Excel can convert this easily.
Similarly, using the standard date function, this can be easily achieved in SAP HANA by creating a calculated columns in a SAP HANA calculated view.
Here is the source data that I used as input data for the table used in the calculation view.
For those who are new to the concept of JD Edwards date (like me), here is a brief intro.
In JDE, the format for Date is C-YY-DDD. (C for Century, YY for the two digit year and DDD for a number between 1 to 365 (or 366 for leap year)) Since JDE was invented in the 20th century (obviously), I believe they started this format which supports for only dates starting year 1900, (as I understand, JDE does not support dates before the year 1900).
Here are a few examples of JDE dates and their equivalent Gregorian date.
115141 - May 21, 2015
102317 - November 13, 2002
99054 - February 23, 1999
So basically if the JDE date is 5 digit, C in the CYYDDD is 0 (zero), which indicates that the date is in the 20th century (i.e. starting year 1900 and before year 2000), and if it starts with 1 (C in CYYDDD – century), then it is in the 21st century starting year 2000.
It looks like a difficult conversion at the first look, but in fact, it is simple on a little bit of digging and the date function of Microsoft Excel can convert this easily.
Similarly, using the standard date function, this can be easily achieved in SAP HANA by creating a calculated columns in a SAP HANA calculated view.
Here is the source data that I used as input data for the table used in the calculation view.
The data type used in the JDE source system was Numeric with precision of 6 and scale of 0 (zero) for the JDE date field as you can see in the image below.
Since the conversion formula uses some numerical calculations I used the source data with “integer” (INT) data type for the JDE date data while loading it into SAP HANA table.
Below is the table data definition, for which I have used only three column table with the data types as shown
As mentioned before, for the JDE date fields, I have used INT data type. I created a simple dimensional calculation view on top of this table.
With that done, all I have to do was create a calculated column which will convert the JDE date into a Gregorian date column with the correct formula.
The definition of the calculated column is done and below is the formula that I used for the JDE date to Gregorian date conversion
date(leftstr(string("UpdateDate"+1900000),4))+int(rightstr(string("UpdateDate"),3))-1
Adding the value 1900000 to the JDE date data – and stripping the leftmost 4 characters (after converting it into a string) brings the correct year. And after that it is as simple as adding the digits to arrive at the correct month and date from the JDE date.
As you can see, the breakdown is very simple from the formula and it brings the output in the calculated column as expected.
The interesting power of the column engine date() functions simply generates the output needed. It does give lot of flexibility to calculate the value as needed in any customized environment.
Here is the output in the date preview with the calculated columns in place.
Secondly – If the DDD number goes beyond 365 (or 366) as you can see in some test data in the PymtDate - it calculates the correct year based on the DDD number, which is not normal for standard date data - but shown here to see how the formula works, and a standard functionality of the date() function for adding days.The DDD is expected to be under 366.
Also - the standard formula with full addition of YYYYMMDD can also be used which also produces the same result as expected, though I feel the additional concatenation as unnecessary to speed up the calculation
date(leftstr(string("UpdateDate"+1900000),4)+'01'+'01')+int(rightstr(string("UpdateDate"),3))-1
Source: scn.sap.com
No comments:
Post a Comment