Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

AS400 Query

Posted on 2011-02-10
6
Medium Priority
?
972 Views
Last Modified: 2012-05-11
I created a query retrieve media information from BRMS. I grabbed the Creation and Expiration Fields  which is in a date format.  I hit F5 to run report I get commas in the field and its not in a date format (mmddyy). Can someone telll me how to change it into MMDDYY?
0
Comment
Question by:gwickers
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34871013
Is it a field of type date, type numeric or type Character?

What format is it ?  YYMMDD (international  or ISO) or an othe rformat?

If it's a date field, just change the editcode
 (find it by option : Specify report column formatting)

if it is a Numeric filed, make a calculation

YYMMDD / 1  =MMDD (4.0)
YYMMDD / 10000 = YY (2.0)
MMDD * 100 + YY = MMDDYY



if it is Character, substring it and concat.

If you need an other format as input let me (us) know

0
 
LVL 13

Accepted Solution

by:
_b_h earned 1000 total points
ID: 34871020

Assuming that you are using QA1AMMM file in BRMS; if not, this example will get you started.
You can create result fields to do what you need.
Convert the numeric field to character:
CREATEDD    digits(tmccrt)
Format the output:
CREATEDMDY  substr(createdd, 4, 2) || '/' ||  
            substr(createdd, 6, 2) || '/' ||  
            substr(createdd, 2, 2)            

Sample output:
  Creation   CREATEDD  CREATEDMDY
  Date                            
                                 
 1,061,210   1061210    12/10/06  
 1,061,229   1061229    12/29/06  
 1,061,229   1061229    12/29/06  
 
Post back any questions!
Barry
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 34871051
If it is displaying with commas, then it isn't a date field, it is a numeric field.  This means that you have to "build your own date".  What format is it currently in?

For example, if it is yyyymmdd format, you could create a result field where you substring out the yyyy mm and dd components and reassemble them in the desired sequence with the desired delimiter.

- Gary Patterson


Field       Expression                         Column Heading       
 TEMP        substr(yyyymmdd,5,2) || '/' ||                          
             substr(yyyymmdd,7,2) || '/' ||                          
             substr(yyyymmdd,1,4)

Open in new window

0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 35

Expert Comment

by:Gary Patterson
ID: 34871066
Oops!  Barry must've posted while I was still typing!  

Points to him and not me, please.
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 34871149
Hi gwickers,

If it's about numerci fieds, barry is right, but because the comma's in the CreationDate, I assume it's numeris, in that case substring cant be used, so my example does the same.
If it is numeric and you like to insert the '/' between the elements, you can also change the editcode or edit word.
 (find it by option : Specify report column formatting)

Good Luck,
0
 
LVL 35

Expert Comment

by:Gary Patterson
ID: 34871240
Barry converted to character using the DIGITS function first, then did a substring.  That'll work.  Or you can do it all in one expression:

TEMP        substr(digits(yyyymmdd),5,2) || '/' ||                          
             substr(digits(yyyymmdd),7,2) || '/' ||                          
             substr(digits(yyyymmdd),1,4)

There is no edit work or edit code that can rearrange a yyyymmdd date into a mm/dd/yyyy date, so that's not really an option.  If yyyy/mm/dd is an option, they you can use an edit code or edit word to get the right delimiter in.

- Gary
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

A while back when OPSMGR 2012 was released we were very excited about getting it into our environment and upgrading our 2007 implementation,  we started our planning and we then proceeded with our implementation. All went as planned & our system …
I originally wrote this article to compare SARDU and YUMI, but have now added Easy2Boot, since that is the one I currently use and find the easiest to create and alter.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

650 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