Plan for failures, hope for success!
Converting Julian Date to Gregorian Date
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
|
1 |
<span style="color: #ff00ff;">dateadd</span>(d,(<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">int</span>,JlnDate) % 1000)-1,<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">datetime</span>,(<span style="color: #ff00ff;">convert</span>(varchar,<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">int</span>,JlnDate) /1000 + 1900) + '/1/1'))) as GrgDate |
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
|
1 |
Select ID, Name, Unit, Description, JlnDate from SQLTable |
to this
|
1 |
Select ID, Name, Unit, Description, <span style="color: #ff00ff;">dateadd</span>(d,(<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">int</span>,JlnDate) % 1000)-1,<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">datetime</span>,(<span style="color: #ff00ff;">convert</span>(varchar,<span style="color: #ff00ff;">convert</span>(<span style="color: #0000ff;">int</span>,JlnDate) /1000 + 1900) + '/1/1'))) as GrgDate from SQLTable |
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.
Comments are closed.