I need to convert a julian date(YYYYDDD) to MMDDYY using WRKQRY

I have a file that has records that only contain dates in julian format. (i.e. 2006082).

Can you, using Query, create a result field for the julian date and have it contain the
MMDDYY equivalent of the julian date?

2006082 would be converted to 032306

OS/400 release V4R3M0


Phillip KnoxSenior Systems AnalystAsked:
Who is Participating?
I don't think you need to change job dates....

Assuming your normal job date etc. is already set as MMDDYY,   (I'm in the UK so job date is DDMMYY)

set up the result field
DATMDY      date(substr(digits(JULDAT),2,7))

where JULDAT is the julian date....

This should create a date field with the date displayed in the job date format...

the easiest way I can think of doing it is via a conversion file, and link that to the query.

the file would contain


you cluld also hold other date information and use it as a generic conversion.


I haven't used Query/400 for quite a few years nor would I ever willingly go back to it. Even without the SQL Dev Kit installed, I'd still use QM queries.

I'm no longer good enough with Query to remember if this kind of thing is possible. I sure don't have any memories of it, but I have seen some surprising techniques. So, I can't say it's not possible. I can say that it's not a reasonable thing to do even if it is possible. Dave's suggestion is probably better than trying to do it all within Query/400.

I'd consider going a different way though. Here's an example program that does the conversion:

------------ Begin copy
  /* From an input date (YYYYDDD),        */
  /*   the MMDDYY conversion is done...   */
  /*   and returned to the caller...      */

pgm    ( +
         &JulDat      +
         &MMDDYY      +

   dcl   &JulDat        *char        7
   dcl   &MMDDYY        *char        6

   dcl   &RtnVal1       *char        4

   callprc      CEEDAYS      ( +
                               &JulDat        +
                               'YYYYDDD'      +
                               &RtnVal1       +
                               *omit          +
   callprc      CEEDATE      ( +
                               &RtnVal1       +
                               'MMDDYY'       +
                               &MMDDYY        +
                               *omit          +



------------ End copy

Not much to it and most of it is white-space. Much of it is simply continued lines. Practically speaking, it's just two CALLPRC commands.

Now, that doesn't do your Query much good by itself. However, if a small program such as this is turned into a SQL function, then it can be referenced in a SQL VIEW. And that should make it available in a Query.

Any chance of going that route?

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Check out the Query for iSeries Use manual at:
On page 82 of the manual, Example 3 is for converting from YYDDD to MMDDYY.
You use CHGJOB to change the default job date format, and then run the query. The DATE function does the conversion.
After the query runs, use CHGJOB to reset the date format back.

If you have any questions on how to implement this, please post back!
Phil, I tried the query on my v4r3 system, and it works.
The julian date in my physical file is named yddda, type character, length 7.
I created a query with these result fields:
NEWDATE     date(yddda)
YY                year(newdate)
MM              month(newdate)
DD               day(newdate)

Before running the query, I changed the default date format for my job with:
chgjob datfmt(*jul)


I tested Tony's suggestion and he is right. No chgjob required
Cheers for the vote of confidence bh....  

is your system/job set to DD/MM/YY or MM/DD/YY  ???

I was just following the example in the manual, but question concerned v4r3, so that was why I was testing and made the comment on your suggestion. No intention of tarnishing your good character.

That system is MDY....DMY is the standard in Europe? I have a couple of pharma customers from UK that use it.

We Canucks seem to follow the Americans, although we did manage to go metric.
Phillip KnoxSenior Systems AnalystAuthor Commented:
Thanks to all who contributed to this thread.

Tony, you da man!

It worked perfectly!

Phil thanks for the points and the grade...

bh - Sorry I misled you - I didn't think you were tarnishing my character -
and I wasn't being sarchastic... - I really did appreciate you checking my code and verifying that it worked....

I quite often check other experts answers as I am interested in learning other ways of doing things - more than one way to skin a cat.....   (not that I would skin a cat - it is just a saying !!!!     ;-)


Why is:

DATMDY      date(substr(digits(JULDAT),2,7))

..."2,7" instead of "1,7"? Shouldn't the DIGITS() function return a 7 character value? (Which brings up why the SUBSTR() is needed as well.)


i dont have a clue why, but in our system Julian date looks not like here, say, 2009158, but as 109158. So in my case the solution is  date(substr(digits(JULDAT+1900000),2,7))
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.