Converting from Gregorian to Julian Dates For JDE
-
Because JD Edward uses Julian Dates from 01/01/1900, you can use this code for converting
CONVERT(VARCHAR, datepart(yy,getdate())-1900) +
CASE WHEN datepart(dy,getdate()) < 10
THEN CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , datepart(dy,getdate()))
WHEN datepart(dy,getdate()) < 100 and datepart(dy,getdate()) > 9
THEN CONVERT(VARCHAR , 0) + CONVERT(VARCHAR , datepart(dy,getdate()))
ELSE CONVERT(VARCHAR , datepart(dy,getdate())) END,
-
You might want to put this in the script library.
.
-
JDE does not use traditional julian date format. What they use is actually a five digit representation of YYDDD which is the two digit year (03) followed by the day of the year (001-365).
So, what you would really want would be to take Jan 1 of the year indicated, and a dateadd of the number of days minus 1, to get the correct conversion.
and to go the other way, the last two digits of the current year concatenated to the datediff in days from jan 1....
Just a FYI
Edited by - scorpion_66 on 04/03/2003 6:11:06 PM
-
Thanks for your response. I am quite new to T-SQL so if the is a better way to do what I am doing please advise. How do I put this script in the library? Also I am sure that JDE use the 6 digit Julian date, at my company, the JDE System certainly uses 6 digits so today would be 103094
-
That would be the expanded format where the have a indicator for century, thanks to the y2k rollover. Your date you give reference to would tranlate to the 94th day of the year 2003.
In JDE, there is a function to show this, if you have access to it, so you could verify it easy enough, though to be honest, I never pulled it up myself, just got the JDE guys to show me, during the course of translating the data for my SQL Server, so I couldn't tell you how to get at it.
-
Not sure if anyone is ever coming back to this thread but having just worked on migrating JDE data out and found the above useful - here is the script to go from JDE to 'Normal' dates
declare @date2 int
select @date2 = 111001
select @date2,
dateadd(dd,convert(int,right(@date2,3))-1,
dateadd(yyyy,((@date2 - convert(int,right(@date2,3)))/1000),0))
-
nice alistar; only thing i would suggest is an improvement to avoid converting from int-to string and back to int again...you can use integer division and modulous to get the same results:
/*--Results
2011-01-01 00:00:00.000
2010-10-24 00:00:00.000
2011-04-27 00:00:00.000
*/
With mySampleData
AS
(
select 111001 AS jdate UNION ALL
select 110297 AS jdate UNION ALL
select 111117 AS jdate
)
SELECT dateadd(dd,(jdate % 1000) -1,dateadd(yyyy,(jdate / 1000),0))
FROM mySampleData
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
I have been using the following to convert to JDE:
Select (DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())
If you don't stand for something, you'll fall for anything!,Don Urquhart
-
Don Urquhart (11/22/2011)
I have been using the following to convert to JDE:Select (DATEPART(yy, getdate()) - 1900) * 1000 + DATEPART(dy, getdate())
This works great, thanks!
-
You're welcome! Glad I could help!
Thx for the feedback.
If you don't stand for something, you'll fall for anything!,Don Urquhart
- alistair_goodall wrote:
Not sure if anyone is ever coming back to this thread but having just worked on migrating JDE data out and found the above useful - here is the script to go from JDE to 'Normal' dates declare @date2 int select @date2 = 111001 select @date2, dateadd(dd,convert(int,right(@date2,3))-1, dateadd(yyyy,((@date2 - convert(int,right(@date2,3)))/1000),0))
Heh... it's been a while. The following code is almost identical to Lowell's good code but it eliminates one subtraction operation by referring to the day before 1900. Both the conversion from DATETIME to JDE dates and the conversion from JDE dates to DATETIME are included, which is what Lowell's code also did. I used a single example instead of 3 so that someone could easily turn it into an iTVF.
--===== Create a JDE Date from "today's" date.
DECLARE @Today DATETIME
,@JdeDate INT
;
SELECT @Today = '2022-12-19 01:31:03.850' --From a GETDATE() converted just for a stable example
,@JdeDate = DATEDIFF(yy,0,@Today)*1000+DATEPART(dy,@Today) --Again, just for a stable example.
;
--===== Show the content of the variables.
SELECT [@Today] = @Today
,[@JdeDate] = @JdeDate
;
--===== Formula to convert the JDE date back to a normal DATETIME
-- If you want it to be a DATE datatype, convert it one more time.
-- "-1" is the DATE SERIAL NUMBER for the day before 1900-01-01, which effective subtracts 1 day.
SELECT NormalDATETIME = DATEADD(dy,@JdeDate%1000,DATEADD(yy,@JdeDate/1000,-1))
;Here are the results:
Lowell's good code stood the test of time. I just made a minor tweak with the -1 thing.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
https://www.sqltopia.com/algorithms/date-and-time/jdedwards/
N 56°04'39.16"
E 12°55'05.25" - SwePeso wrote:
https://www.sqltopia.com/algorithms/date-and-time/jdedwards/
You should have posted those here back in 2009. 😀
I also noticed the note at the bottom of your post... why not write those functions as iTVFs instead of Scalar to begin with. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply