topic JDE Date to Standard Date Format in QlikView

JDE Date to Standard Date Format

Thu, 10 Nov 2011 02:07:12 GMT

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

JDE Date to Standard Date Format

dzrtrdr — Thu, 01 Dec 2011 03:03:44 GMT

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!

Re: JDE Date to Standard Date Format

dzrtrdr — Thu, 01 Dec 2011 03:08:54 GMT

Mary,

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!

Re: JDE Date to Standard Date Format

Anonymous — Thu, 22 Dec 2011 13:44:44 GMT

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

JDE Date to Standard Date Format

tfidler240 — Fri, 03 Feb 2012 15:00:34 GMT

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.

Re: JDE Date to Standard Date Format

Marc_Kaiser — Fri, 03 Feb 2012 15:23:07 GMT

And here another one 🙂

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

Re: JDE Date to Standard Date Format

Mon, 09 Jun 2014 17:06:12 GMT

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!

Re: JDE Date to Standard Date Format

tfidler240 — Mon, 09 Jun 2014 18:24:01 GMT

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

Re: JDE Date to Standard Date Format

aritting — Thu, 28 Apr 2016 13:37:31 GMT

THANKS!

Re: JDE Date to Standard Date Format

Matt45 — Mon, 04 Jan 2021 23:51:57 GMT

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.

Re: JDE Date to Standard Date Format

Matt45 — Tue, 05 Jan 2021 03:04:35 GMT

My initial hunch was right. The Julian dates I have from my data source is the Unix variant, meaning the starting reference date is actually 1970-01-01. The conversion of the date to seconds since the reference date is (JD − 2440587.5) × 86400.

To convert this to a date, I converted to seconds, converted to days, then added the days to 1970-01-01 within the date function to get my date in the data load editor:

My_Table: LOAD OBJECTID, ([DISCOVERY_DATE]- 2440587.5)*86400 AS SecondsNum, FROM "Table"; tJulian_Dates: LOAD [OBJECTID], [DISCOVERY_DATE] as JDE, SecondsNum/86400 as DaysNum RESIDENT Table; Julian_Dates: LOAD *, Date('1970-01-01' + DaysNum) as MY_DATE, RESIDENT tJulian_Dates; DROP TABLE tJulian_Dates;