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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?

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!
Learn Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
IBM System i

From novice to tech pro — start learning today.