?
Solved

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

Posted on 2006-03-23
12
Medium Priority
?
12,118 Views
Last Modified: 2011-08-18
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

Thanks,

Phil
0
Comment
Question by:pipster1
  • 4
  • 3
  • 2
  • +3
12 Comments
 
LVL 14

Expert Comment

by:daveslater
ID: 16271813
Hi
the easiest way I can think of doing it is via a conversion file, and link that to the query.

the file would contain

YYYYJJJ
MMDDYY

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

Dave
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16276549
Phil:

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          +
                             )

Exit:

   return

endpgm
------------ 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?

Tom
0
 
LVL 13

Expert Comment

by:_b_h
ID: 16277624
Phil,
Check out the Query for iSeries Use manual at:
http://publib.boulder.ibm.com/infocenter/iseries/v5r3/topic/books/sc415210.pdf
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!
Barry
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 13

Expert Comment

by:_b_h
ID: 16277647
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)

Barry

0
 
LVL 9

Accepted Solution

by:
ShadowProgrammer earned 2000 total points
ID: 16279296
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...


Tony.
0
 
LVL 13

Expert Comment

by:_b_h
ID: 16279461
I tested Tony's suggestion and he is right. No chgjob required
bh
0
 
LVL 9

Expert Comment

by:ShadowProgrammer
ID: 16279604
Cheers for the vote of confidence bh....  

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

Tony.
0
 
LVL 13

Expert Comment

by:_b_h
ID: 16279935
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.
Barry
0
 

Author Comment

by:pipster1
ID: 16280282
Thanks to all who contributed to this thread.

Tony, you da man!

It worked perfectly!

Phil
0
 
LVL 9

Expert Comment

by:ShadowProgrammer
ID: 16285349
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 !!!!     ;-)


Tony
0
 
LVL 27

Expert Comment

by:tliotta
ID: 16286283
Meanwhile...

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

Tom


0
 
LVL 2

Expert Comment

by:sunny-s
ID: 25959625
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))
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
Most folks would know the basics of how Dropbox works, so that’s not the purpose of this article. Security is what it’s all about, so here I’ll share how I choose to secure my Dropbox Account and the Data it contains.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month14 days, 1 hour left to enroll

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question