Peter Larsson Blog

Patron Saint of Lost Yaks

JDEdwards date conversion

It seems JDEdwards dates are stored NUMERIC(6, 0) in this format 107299, where 107 is the number of years after 1900, and 299 is the 299th day of the year.

So how to convert JDEdwards to DATETIME?


DECLARE @jde NUMERIC(6, 0)

SET     @jde = 107299

SELECT @jde,

        DATEADD(DAY, @jde % 1000, DATEADD(YEAR, @jde / 1000, -1))
 
And how to go from DATETIME to JDEdwards?

DECLARE @Date DATETIME

SET     @Date = '20041117'

SELECT @Date,
        1000 * DATEDIFF(YEAR, 0, @Date) + DATEPART(DAYOFYEAR, @Date)
 

Legacy Comments


Jasom
2010-06-30
re: JDEdwards date conversion
JDE and it's Julian dates have become a daily headache for me. I follow your blog and noticed that you tend to create functions. For the Gregorian date conversion, why did you decide not to? Also for large data sets, would a function minimize performance issues (besides utilizing a view)?

Peso
2010-06-30
re: JDEdwards date conversion
Write a function which takes "@jde NUMERIC(6, 0)" as parameter, and return datatype is datetime.

Ted
2011-10-06
re: JDEdwards date conversion
Thank you, worked perfectly!