JDE Date to Standard Date Format

[#1]

How do you convert a JDE date (CYYDDD) into MM/DD/YY?

[dzrtrdr]

Mary,

JDE uses a Gregorian number, which is comprised of years and days.  The right-most three numbers represents the number of days into the year (001 being the 1st day of the year). The numbers to the left of the third position represents the number of years since 1900.  Replace "YourDateField" below with the JDE date field name and it should give you what you are looking for.

The AddYears function is adding the appropriate number of years to 1900.  In some cases, the Gregorian date may not always be six numbers, so you need to determine the lenth first, then subtract 3 from it.

The next part of the expression is adding the days to Jan 1st of year just calculated. And since 001 represents the 1st day of year, adding 001 to Jan 1st would obviously be Jan 2nd, so we subtract 1 to arrive at the correct date.

There may be other that have a simplier way to convert the date, but it does work nevertheless.

AddYears('1/1/1900',num(left(YourDateField,len(YourDateField)-3)))

+num(right(YourDateField,3))-1

Enjoy!

[dzrtrdr · Accepted solution]

Mary,

JDE uses a Gregorian number, which is comprised of years and days.  The right-most three numbers represents the number of days into the year (001 being the 1st day of the year). The numbers to the left of the third position represents the number of years since 1900.  Replace "YourDateField" below with the JDE date field name and it should give you what you are looking for.

The AddYears function is adding the appropriate number of years to 1900.  In some cases, the Gregorian date may not always be six numbers, so you need to determine the lenth first, then subtract 3 from it.

The next part of the expression is adding the days to Jan 1st of year just calculated. And since 001 represents the 1st day of year, adding 001 to Jan 1st would obviously be Jan 2nd, so we subtract 1 to arrive at the correct date.

There may be other that have a simplier way to convert the date, but it does work nevertheless.  You can then wrap this expression with Date(xx,'MM/DD/YY') to dress it up.

AddYears('1/1/1900',num(left(YourDateField,len(YourDateField)-3)))+num(right(YourDateField,3))-1

Enjoy!

[Anonymous]

Hi, Mary,

Another way to convert JDE Date to normal one is:

Date(MakeDate(1900+Floor(JDEDate/1000))-1+Mod(JDEDate,1000))

In case a JDEDate field could be empty or with '0' value, an IF statement need to be added:

If(Len(Trim(JDEDate))>4,Date(MakeDate(1900+Floor(JDEDate/1000))-1+Mod(JDEDate,1000)))

Rgds,
AT

[tfidler240]

I got this from Qlikview a couple of years back and have converted all of my date conversions to work in Version 9 and above.

date('1/1/' & text(left((109202 = 1900000), 4)) + right((109202 + 1900000),3) -1 AS [NEW DATE]

109202 would be the date field such as SDIVD in JDE.

I works great for me.

[Marc_Kaiser]

And here another one 🙂

If (SHTRDJ>0, (date('01.01.' & left((SHTRDJ + 1900000), 4) + right(SHTRDJ, 3) - 1)), ' ') AS [Order Date Header],

[#7]

Hi, I'm trying to convert a Julian date (like this 2455825) to a regular date (20/09/2011), but the formulas you gave are not working for me. Can you help me why is different and what can I do?

I'm working with BMC Remedy ARS application on a SQL Server database.

Thanks!

[tfidler240]

The date fields that are used in JD Edwards software are stored as CMMDDD (Cjulian) format. This is unique to the software. Your date stored in JDE would be 111263 in database.

Sorry,

Toney Fidler

Director of MIS

CANTEX, Inc.

301 Commerce St, Suite 2700

Fort Worth TX 76102

tfidler@cantexinc.com<mailto:tfidler@cantexinc.com>

817-215-7029

[aritting]

THANKS!

[Matt45]

Hi, I've tried all the solutions above and none of them seem to work. I have a 7 digit Julian date with a decimal. How do I convert these?

For example I have these dates and their converted dates below. I verified these are the correct converted dates using this calculator https://www.onlineconversion.com/julian_date.htm.

The digits in the Julian dates here don't seem to match up with what is explained in the top post.

Julian Date    Converted Date
2457291.5 --> 9/26/2015
2457300.5 --> 10/5/2015
2457144.5 --> 5/2/2015
2457309.5 --> 10/14/2015
2457095.5 --> 3/14/2015

I believe these dates might be a unix variant, but I tried the solution suggested here https://community.qlik.com/t5/QlikView-Scripting/Julian-Date-conversion-in-the-script/td-p/1002316 , and that also doesn't work.