The Mystery of JDE Julian Date Format [SOLVED]

It was a Saturday night, I was working in night shift solving issues and learning new things about JDE. I came across a mail where some user had asked the output of date column in DD/MM/YYYY format instead of Julian Date Format. I wondered what was wrong with the user and why he dint do it himself. As there were hardly any noteworthy issues (that being a Saturday night), I decided to dig deeper into this issue. Little did I realise that I was going to spent the entire night writing SQL queries and failing every time.

So after a deep research (considering the length of articles on Oracle docs) of nearly 15min, I finally found how JDE Julian Date Format is represented!

What is JDE Julian Date Format?

JDE Julian Date Format is the format in which the date is stored into database. It is a 6 digit number, that can be used to represent a date between 1st January 1900 to 31st Dec 2899.

JDE Julian Date Format is represented as :

CYYDDD

Where,

• C stands for Century
  0 -> 19+0 -> 1900
  1 -> 19+1 -> 2000
  .
  .
  .
  9 -> 19+9 -> 2800
• YY stands for the year of that particular century.
• DDD stands for the day of that particular year of that particular century.

I know, I know, I can understand since I had felt the same way. Anyways, let’s keep our views aside and see how this works.

So suppose you are told to convert 1st January 2014 into “JDE Julian Date”, what are you supposed to do?

Simple,

• Check the century, in this case its 21st i.e. 2000. So 2000 -> (19+1)*100
• Hence C = 1
• Now year is obviously 2014-2000 = 14.
• And 1st January is 1st day of the year, so we will represent it as 001.
• Thus 1st January 2014 = 114001 as per JDE Julian Date Format.
• Similarly 2nd January 2014 = 114002 and so on.

Similarly, if you are asked to convert JDE Julian Date to DD/MM/YYYY format. Say you are given the JDE Julian Date as 113338, use the following steps to acheive the desired result :

• Take first digit and add 19 to it, multiply the sum by 100. In this case you will get (19+1) * 100 = 2000, where 1 comes from 113338.
• Take the next two digits and add to the result obtained in previous step. In this case 2000 + 13 = 2013.
• Now take the last 3 digits, open your calendar and browse to the year obtained in above step. Then start counting. In this example you will reach the date 4th Dec 2013 i.e. 04/12/2013.

I will be soon publishing a page where you can convert the dates online and also give tricks to do it offline. But untill then use these tips to convert to and from JDE Julian Dates. Hope this article saves you time and efforts.

Edit :

I finally got time to code the tool to convert JDE Julian Date to Simple DD/MM/YYYY format and vice versa.

It is available here ->JDE Julian Date Converter Online v1.0

Just save the file anywhere in your computer and open it in browser to use it anytime.