Convert JDE Julian Date to Gregorian SSRS

Question

Post reply

Convert JDE Julian Date to Gregorian SSRS

mbrady5

SSCrazy

Points: 2835
More actions
June 20, 2012 at 12:03 pm

#392566

Does anyone know of an easy method to convert a Julian Date from JD Edwards to Gregorian? I would like to do this in a reporting srvcs project. Thanks

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Don't know what a JD Edwards Julian Date is so I couldn't at the moment.

mbrady5 SSCrazy Points: 2835 More actions · Answer 2

What I mean is to convert a date from julian to gregorian. Example would be ; Julian - 112074 convert to 03/14/2012 thanks

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Try this:

DECLARE @jdejulian INT;

SET @jdejulian = 112031;

SELECT

DATEADD(dd, CAST(RIGHT(RIGHT('0' + CAST(@jdejulian AS VARCHAR), 6),3) AS INT) - 1, DATEADD(yy, CAST(LEFT(RIGHT('0' + CAST(@jdejulian AS VARCHAR), 6),3) AS INT), 0));

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Lynn Pettis

SSC Guru

Points: 442467

June 20, 2012 at 12:19 pm

#1503634

Working on another way at this time as well.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Here is a better way:

DECLARE @jdejulian INT;

SET @jdejulian = 112074;

SELECT DATEADD(dd, (@jdejulian % 1000) - 1, DATEADD(yy, @jdejulian / 1000, 0));

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

And to convert a date >= 1900-01-01 to a JD Edwards Julian date:

DECLARE @ThisDate datetime;

SET @ThisDate = GETDATE();

SELECT (YEAR(@ThisDate) - 1900) * 1000 + DATEPART(dy, @ThisDate);

mbrady5 SSCrazy Points: 2835 More actions · Answer 7

Thank you for your replies. Do I add this in the custom code area?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

How you implement this in reporting services or JD Edwards code, I'm not sure. What I provided can be used in T_SQL queries.

Convert JDE Julian Date to Gregorian SSRS

Cookies on SQLServerCentral