Solved

AS400 Query

Posted on 2011-02-10
6
961 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
  • 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 250 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 34

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 34

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 34

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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

One good source is the manufacturer's web site. Even though the device operating system is changing, the hardware isn't. Going to the support site and looking at the Vista drivers provides a starting point for identifying system hardware for which d…
I previously wrote an article addressing the use of UBCD4WIN and SARDU. All are great, but I have always been an advocate of SARDU. Recently it was suggested that I go back and take a look at Easy2Boot in comparison.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

803 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