JDEdwards seem to be a popular application now owned by Oracle. For some reason, JDEdwards opted for a proprietary storage format for dates in their application. Unfortunately, this storage format is mistaken for Julian date.
Also, JDEdwards opted for a datatype NUMERIC(6, 0) which in SQL Server need 5 bytes, where an INT (4 bytes) would have been a better choice. I have seem some JDEdwards applications in SQL Server and for some unknown reason, the date columns (who are NUMERIC(6, 0) in Oracle) are NUMERIC(18, 0) in SQL Server. I believe when Oracle scripted out the database, they didn’t provide PRECISION and SCALE to NUMERIC and hence SQL Server defaulted to NUMERIC(18, 0). This is really bad for performance since now the same information need 9 bytes instead of 5 bytes (or 4 if using INT). The tables will be very bloated and tests I’ve made revealed the storage need for the table dropped by 40%, just by converting the NUMERIC(18, 0) columns to NUMERIC(6, 0).
A date in JDEdwards is a composite value of the year (as an offset from 1900) and the day number within the year. For example, a JDEdwards date value of 118011 corresponds to today’s date of January 11th, 2018 (2018-01-11 in ISO date format and gregorian calendar). A JDEdwards date value of 99249 corresponds to the date September 6th, 1999.
So how can we convert between these to date formats as efficiently as possible?
Well, this is the way to do it.
Please pay attention the the scalar functions above. If you want performance in SQL Server, you should rewrite the function as Inline Table Valued Function instead and use CROSS APPLY to access the function. Scalar functions in SQL Server (as of version 2017) prohibits parallelism when used in a statement.