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.

14 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.

  7. asd says:

    Hello, I want to subscribe for this webpage to get hottest updates, thus where can i do it please help.

  8. roma ostelli says:

    Thank you, I’ve recently been searching for information approximately this subject for a long time and yours is the best I have came upon so far. But, what concerning the conclusion? Are you positive concerning the supply?

  9. Mike says:

    Thanks for tips! I appreciate it!

  10. Anonymous says:

    You ought to be a part of a contest for one of the
    most useful sites on the internet. I will highly recommend
    this website!

  11. MDF 797 says:

    One particular organization which includes good quality products and solutions and regularly has
    gross sales on their own stethoscopes is Littmann by 3M.
    Therefore, doctors can use this stethoscope to listeen to faint heart appears of an obese affected person as well aas strong heart seems of a child wth exceptional clarity.
    This instrument attributes two-sided chest parts with non-chill rims
    and diaphragm for affected individual well-being and luxury.
    You may also select the colour of the tubing for the quantity of products.

    I had been actually shock to be aware of that a toy this
    well-liked wasn’t inside the media or how occur
    I never read of it.

  12. クロムハーツ アパレル 通販 says:

    クロムハーツ アパレル 通販…

    単にアメリカン·エキスプレスによるキャッシュは、すべてのニーズを満たします。アメリカン·エクスプレスからの偉大なアメリカン·エキスプレスでは、単純に、キャッシュからの偉大…

  13. costa rica fishing charters says:

    This is not a suit only worn when something unlikely is happening.

    You can always ask the sporting goods store to assist you.
    Investigation everything concerning your angling journey upfront.

  14. Tressa says:

    I think everything wrote was very reasonable.
    But, think about this, what if you were to write
    a killer headline? I mean, I don’t want to tell you how to run your website, but what if you added something that makes people desire more?
    I mean JD Edwards Date Conversions (CYYDDD) | Kirix Strata
    Blog is a little vanilla. You ought to glance at Yahoo’s home
    page and see how they create news headlines
    to grab people to click. You might try adding a video or a related
    picture or two to get people excited about everything’ve written. In my opinion, it could
    bring your posts a little livelier.

Leave a Reply