Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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)
 

Print | posted on Friday, July 10, 2009 4:23 PM | Filed Under [ Optimization SQL Server 2008 Algorithms SQL Server 2005 SQL Server 2000 ]

Feedback

Gravatar

# 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)?
6/30/2010 9:56 AM | Jasom
Gravatar

# re: JDEdwards date conversion

Write a function which takes "@jde NUMERIC(6, 0)" as parameter, and return datatype is datetime.
6/30/2010 10:28 AM | Peso
Gravatar

# re: JDEdwards date conversion

Thank you, worked perfectly!
10/6/2011 7:47 PM | Ted
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET