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,
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! |