RSS

How to convert EnterpriseOne (JDE) Julian Dates

16 Aug

Overview

If you’ve ever worked with Oracle/PeopleSoft’s EnterpriseOne ERP system (also known as JD Edwards or simply JDE) you’ll quickly find that dealing with dates from the database is a bit challenging.  EnterpriseOne stores all dates in the system in Julian Date format.  EnterpriseOne has a lot of dates in every table.

The Julian Date in EnterpriseOne goes back to the days before EnterpriseOne was called EnterpriseOne; back when it was just JD Edwards and it ran on an AS/400 (now called an iSeries or Series i).  Back then, disk space (DASD) was a premium.  By storing all the dates in 6-digit Julian format, the numbers could be packed (compressed) to save table or database space.  Additionally, since Julian Dates are in a fairly simple numerical form, it was easy to develop routines and functions to add, subtract, and otherwise calculate dates.

Over the years, disk space has turned into a commodity.  Modern computer languages and SQL  can easily deal with date calculation.  But EnterpriseOne data is still in this Julian Date format.   So how do we deal with it, and how do we get it converted into a normal datetime data type?

This article will cover how to create and use two SQL functions to convert Julian dates to Gregorian dates and then to convert Gregorian dates to EnterpriseOne Julian format.  Many of my clients use these two functions for SQL Server Reporting Services (SSRS) Reports, and various EDI interfaces, and XML feeds.  These two functions are valid on any version of JD Edwards including World, OneWorld, XE, or the latest version of EnterpriseOne.  If you want to use the functions on World, or directly on an IBM Series i, you’ll need to do a bit of syntax tweaking.  If you’re accessing the Series i (AS/400) from SQL Server, then this function will work perfectly for you.

EnterpriseOne Julian format

All versions of JDE use a Julian Date format as follows: CYYDDD where C = Century; YY = a 2 digit year; and DD = the 3 digit number representing the day of the year (1 through 365 or 366 days on a leap year).  The Century is either a 1 or 0 depending on whether you’re using year 2000 + or if you’re using dates in the 1900′s.

For example, the Gregorian Date 01/01/2009 would be Julian Date 109001 where 1 = century 2000, 09 = the last 2 digits of the year 2009; and 001 = the first day in 2009. Similarly 12/31/2009 would be represented as 109365 where the only change is the day number being 365 to represent the last day in 2009.

Note: A value of zero(0) in JDE Julian date format represents 12/31/1899 and 01/01/1900 is the numerical value 1 in Julian format.

Converting Julian Dates to Gregorian – Date J2G( )

The first function we’re going to write is one called DateJ2G() meaning “Date Julian to Gregorian”. This function has two parameters, one parm for the Julian date you wish to convert, and one parm for the format style code, which represents the format you want your Gregorian date.  The DateJ2G() function will convert the date to any standard SQL Server formatted date style.  A complete list of format styles can be found at http://ow.ly/kdvP.

Here’s the code to create the function:

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[DateJ2G]
 (
@JDEDATE int, @FORMAT int
 )  
RETURNS varchar(20) AS  
--Written by Bryant Avey, InterNuntius, Inc.
--Provided free "As Is" with no warranties or guarantees
--I just ask that you keep these comments in the function, if you use it.
--The complete article describing this function can be found at:
--http://wp.me/pBPqA-a

--This function takes a JDE Julian Date and returns
--a varchar date in the format style you specify
--To us simply pass in the JDE date and the style code
--Style codes can be found at 

--For Example: select dbo.DateJ2G(sddgj,101) from f4211
--would return the JDE date in the format of 02/29/2008.
--Select dbo.DateJ2G(108060, 1) = 02/29/08
--Select dbo.DateJ2G(109060, 107) = Mar 01, 2009

--Format codes are standard SQL 2005 Date Convert codes.
--Conversion codes can be found here: http://wp.me/pBPqA-a
BEGIN
DECLARE @sqldate datetime
set @sqldate =
 dateadd(day,cast((1900000 + @JDEDATE)%1000 as int)-1,(cast((
 cast((1900000 + @JDEDATE)/1000 as varchar(4)) + '-01-01')
 as datetime)))

RETURN (convert(varchar(20),@sqldate,@FORMAT))
END

To use the DateJ2G function, just pass in a date and style format as described in the comments contained in the above code.

Converting Gregorian Dates to Julian – DateG2J( )

The next function will convert any standard formatted Gregorian date string to a JDE EnterpriseOne Julian date.

Here’s the code to create the DateG2J function:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DateG2J] (@Geogian_in datetime)
RETURNS int AS
--Written by Bryant Avey, InterNuntius, Inc.
--Provided free "As Is" with no warranties or guarantees
--I just ask that you keep these comments in the function, if you use it.
--The complete article describing this function can be found at:
--http://wp.me/pBPqA-a

--This function takes a varchar gregorian date and returns
--a Julian JDE Date
--To use simply pass in the string date

--For Example: select dbo.DateG2J('02/29/2008')
--would return the JDE integer date of 108060.

--Date input formats are standard SQL 2005 DateTime values.
--Any validly formated date string will work such as 'feb 29,2008' to get 108060.

BEGIN
declare @JulianDate_out INT
declare @Century INT
declare @YY INT
declare @DayofYear INT

 Select @Century = case when datepart(yyyy,@Geogian_in) > 2000
 then 100000 else 0 end

 Select @YY = CAST((SUBSTRING(CAST(DATEPART(YYYY, @Geogian_in)
 AS VARCHAR(4)), 3, 2)) AS INT)

 select @DayOfYear = datepart(dayofyear, @Geogian_in)

 SELECT @JulianDate_out = @Century + @YY * 1000 + @DayofYear

RETURN(@JulianDate_out)
END

To use the DateG2J function just follow the examples outlined in the code above.

Conclusion

As you can see, there are only a few lines of SQL code needed to convert between Julian and Gregorian dates in JDE EnterpriseOne.  I used February 29, 2008 (leap year) dates in the examples to show that the functions correctly handle leap years.

Let me know how this has worked for you.

About these ads
 
10 Comments

Posted by on August 16, 2009 in TechKnowledgy Articles

 

Tags: , , , , , , , , , , , , , ,

10 responses to “How to convert EnterpriseOne (JDE) Julian Dates

  1. Lyn

    December 29, 2012 at 4:22 pm

    Hi,

    I know this is blog is old but it has been of great assistance for the project that I am working on. Thankyou!

     
    • Bryant Avey

      December 31, 2012 at 12:27 pm

      You’re most welcome Lyn. As old as it is, I still get around 30 hits a day on this post. In fact, we’re doing a project at a client and are using much of the information here for that project. It works the same even with JDE Enterprise 9.x but was written on version 8.x I’ve even used portions of it with old AS/400 – iSeries systems running JDE World A7.3 Cume 11. Of course some of the tables/files are different on the IBM AS/400….

      Thanks for the feedback. I appreciate it.

       
  2. Betsey

    May 22, 2013 at 8:59 am

    Thanks for another informative web site. Where else may just I get that kind of information written in such an ideal way?
    I’ve a venture that I’m just now operating on, and I’ve been on the look out for such info.

     
  3. Stewart Johnston

    July 10, 2013 at 9:05 am

    If you’re wanting to do it in Oracle, this will convert an individual field in a query:
    TO_DATE(TO_CHAR(sdppdj +1900000),’YYYYDDD’) “Promised”

    This will give you today as Julian for a query:
    ((TO_CHAR(sysdate, ‘YYYY’)-1900)*1000)+(TO_CHAR(sysdate, ‘DDD’))

    e.g. The following shows what was promised to the customer before today but hasn’t shipped yet:
    SELECT TO_DATE(TO_CHAR(sdppdj +1900000),’YYYYDDD’) “Promised”,
    TO_DATE(TO_CHAR(sdupmj +1900000),’YYYYDDD’) “Last Update”,
    sddoco “Order”, sddcto “Type”, shhold “Hold”, sdlitm “Item”, sdlotn “Sales Lot”, sdlttr “Last”,
    sdnxtr “Next”, sduorg “Qty”
    FROM proddta.f4211
    JOIN proddta.f4201
    ON (sddoco = shdoco
    AND sddcto = shdcto
    AND sdkcoo = shkcoo)
    WHERE sdnxtr 525
    AND sdppdj < ((TO_CHAR(sysdate, 'YYYY')-1900)*1000)+(TO_CHAR(sysdate, 'DDD'))
    ;

    If you've got your Julian into Excel, try this:
    =DATE(1900+INT(A2/1000),1,MOD(A2,1000))

     
    • Bryant Avey

      July 18, 2013 at 11:17 pm

      Thanks for the feedback and contribution. The Oracle syntax is a bit different, and I haven’t posted it, so thanks the the help. ;-)

       
  4. adult chatroulette

    July 21, 2013 at 10:26 am

    Thanks for your marvelous posting! I actually enjoyed reading it, you may be a
    great author. I will always bookmark your blog and will come back someday.

    I want to encourage you to ultimately continue your great job,
    have a nice weekend!

     
  5. mobile marketing

    July 25, 2013 at 4:22 am

    Hi there I am so glad I found your blog, I really found you by mistake, while I
    was researching on Askjeeve for something else, Nonetheless
    I am here now and would just like to say kudos for a incredible
    post and a all round thrilling blog (I also love the theme/design), I don’t have time to look over it all at the moment but I have bookmarked it and also added in your RSS feeds, so when I have time I will be back to read much more, Please do keep up the great job.

     
  6. andriy

    February 3, 2014 at 8:36 am

    Hi Bryant,
    Thanks a lot for your blog!
    Great functions! I’ve used them and they saved me quite a bit of time!
    Just a quick note: I believe, you may want to change ‘>’ to ‘>=’ in DateG2J to show the dates in year 2000 correctly.

     
    • Bryant Avey

      February 5, 2014 at 4:00 pm

      Thanks for your comment. You’re right. This post is a bit old, but continues to be of pretty high interest. I’ll update the code to reflect your suggestion. Thanks again.

       
  7. Bryant Avey

    January 28, 2013 at 1:33 pm

    Thanks for your comment Dawna. You’re right it took some time to figure this out. But We’ve been with JDE data for nearly 20 years now. So I guess you could say it took 15 years or so, to get this stuff figured out. We still use the content in that post today, with version JDE Enterprise Version 9.x to build enterprise data warehouses. It makes it nice to be able to automatically script out the dates and also to use JDE’s data dictionary to replace all those cryptic column names with descriptions from the Data Dictionary.

    Enjoy it, and thanks again for your comment – sorry it took me so long to reply….

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
InterNuntius

a blog by Bryant Avey

Bamboo Innovator

R.E.S.-ilience in Value Creation 《竹经:经商经世离不得立根创新》

On Purpose Magazine

Inspiring, Educational, Enlightening and Entertaining Content of Value

Wordament

The real-time, continuous word tournament!

Ricky's Bing Maps Blog

A blog focused on developing applications with Bing Maps

a blog by Bryant Avey

JJ's Blog

Microsoft Business Intelligence and SharePoint in Action

SQL Server Rider

Database, SSIS, SSAS, SSRS, PowerPivot, Spatial

Nishant Rana's Weblog

Everything related to Microsoft .NET technology

Jimblog

SharePoint, ProjectServer and Microsoft Platform

Connection Agent

What do you need? Ask Steve!

Choiceology Blog

Just another WordPress.com weblog

Tales from the IT Side

Understanding SharePoint and how it interacts with your organization

Reckless Abandon

...with all your heart, soul, mind, and strength

FoodPress

Serving up the hottest dishes on WordPress.com.

Fortune Tech: Technology blogs, news and analysis from Fortune Magazine

Fortune's tech team offers analysis and perspective on the world's most important developments.

Follow

Get every new post delivered to your Inbox.

Join 1,771 other followers

%d bloggers like this: