Thinking outside the box

Patron Saint of Lost Yaks
posts - 159, comments - 437, trackbacks - 0

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

Feedback

No comments posted yet.

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 8 and 6 and type the answer here:

Powered by: