So if you work with JD Edwards Enterprise One, you know that the dates in the database are saved as Julian Date. Probably not a big deal, until someone requires that you create some sort of view or report based on the database tables for JDE. Well, in order to convert the fantastic Julian date to Gregorian (the dates we are used mm/dd/yyyy) so it’s human readable, then you have have to use a formula or stored procedure, or, you can use the following SQL

 

Now you can quit reading right here if you are happy with just the formula, or you can keep reading and read my explanation on how the SQL works!!

 

Ok so you decided to keep reading…Now Lets assume that we have a table, and the table is structured like so

ID Name Unit Description JlnDate

Now In order to query that table, we can use a simple Select Query, but then again, the field named “JlnDate” has the Julian date value, and that’s what we want to convert, so we modify our query from this

to this

 

And here is the break down of what exactly the SQL is doing…

Lets assume our JlnDate field has a value of 111089

dateadd(d,(convert(int,JlnDate) % 1000)-1,convert(datetime,(convert(varchar,convert(int,JlnDate) /1000 + 1900) + ‘/1/1’))) as GrgDate This Part takes the Julian Date Column and Divides it by 1000 then Adds 1900 to the result

Sample: 111089 / 1000 = 111.089 but since its an Interger, It becomes 111, so 1900 + 111

Final Value 2011

dateadd(d,(convert(int,JlnDate) % 1000)-1,convert(datetime,(convert(varchar,‘2011’ + ‘/1/1’))) as GrgDate This Part takes 2011 as a String and Adds /1/1 making it a valid SQL Date String (2011/1/1) ie Jan 1, 2011
dateadd(d,(convert(int,JlnDate) % 1000)-1,convert(datetime,’2011/1/1′)) as GrgDate This Part turns the Date String to an Actualy Date Time (‘2011/1/1 00:00:00’)
dateadd(d,(convert(int,JlnDate) % 1000)-1,‘2011/1/1 00:00:00’) as GrgDate This part takes the Julian Date Column which is 111089 and does something called MOD or “remainder” (that’s what the % stands for) so it will return 111089 % 1000 = 89
dateadd(d,(89)-1,’2011/1/1 00:00:00′) as GrgDate Now Finally, the DateAdd function will Add (89-1) days (d) to ‘2011/1/1’

The reason for the Minus (-) one is because you cant have a date of zero (0) so the 89th day of the year is actually 88 days after Jan 1st

3/31/2011 And Finally we come up with March 31st, 2011

Thats it. Now you not only have working SQL code to convert Julian Date, but you also know exactly how it works.

Facebook Twitter Email Linkedin Digg Delicious