Kirix Strata Blog

JD Edwards Date Conversions (CYYDDD)

In the last post, we talked about Julian dates and Unix Timestamp conversion.  We recently received a support request regarding another type of date conversion and, since it might be applicable to others, we thought it would be a good idea to show how you can convert it in Strata.  The format is C-YY-DDD, which is used by JD Edwards software: the century after 1900, the year in that century and the Julian date within that year.  Here are a few examples:

105031 - January 31, 2005
107263 - September 20, 2007
98129 - May 9, 1998

To convert the dates we first want to get the string into the proper year and then we’ll add the Julian date to get the proper day/month (which will also take into account leap years).  So, let’s take the example “107263″ (we’ll assume that it is in Numeric format in your field).  Here is the full expression you can use in a calculated field:

date(1900+(field1/1000),1,1)+val(right(str(field1),3))-1

Getting the Year

date(1900+(field/1000),1,1)

The first part of the formula takes the string and divides by 1000, which, when rounded, will provide the first three digits (”107).  It then adds 1900 to the 107 to give us “2007″.  Then the normal date conversion applies and we end up with “01/01/2007.”

Getting the Day and Month

val(right(str(field1),3))-1

Here we are adding the Julian days for the year to the date we created above “01/01/2007″ — so, if we added “1″ to this date, we would get 01/02/2007, and so on.  Here we are using the RIGHT() function to pull out the 3 digits on the right side of the field, in this case “263.”  Because it was numeric, we needed to first convert it to a string using the STR() function and then we converted the result back to a numeric value using the VAL() function.  This gives us “09/21/2007.”  Because we started at “1″ (January 1st) instead of “0″, we simply need to subtract 1 from our date to obtain the correct date of “09/20/2007.”

NOTE:  If your JDE date was a character field instead of a numeric field to begin with, you could change the structure or just convert manually in your expression with the VAL() function as follows:

date(1900+(val(field3)/1000),1,1)+val(right(str(val(field3)),3))-1

If anyone has other date conversions they’re having trouble with, please let us know and we’ll see if we can help.

6 Responses to “JD Edwards Date Conversions (CYYDDD)”

  1. Paul says:

    Thanks for the useful tips, great job guys.

    Paul Lynch, CEO

  2. Gordon says:

    Thanks, that’s really helpful, amazing how little information there is about this format on the t’interweb

  3. Excel Date Conversion (Days from 1900) | Kirix Strata Blog says:

    […] Kirix Strata Blog « JD Edwards Date Conversions (CYYDDD) […]

  4. rajesh says:

    I need help to convert julian date (cyyddd) format to normal date format..

  5. Ken Kaczmarek says:

    Rajesh,

    Please see this post on converting Julian dates:

    http://www.kirix.com/stratablog/converting-julian-dates-and-unix-timestamps

    They’re not in a CYYDDD format, but rather a number of days since January 1, 4713 BC. If you are dealing with something other than Julian, please let me know.

  6. Armando says:

    This data convertion formula looks very clever, however, I could not implemented in SQL Server R2. Error message says ‘DATE’ is not a recognized built-in function name.