Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

AS400 Query

Posted on 2011-02-10
6
Medium Priority
?
975 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 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 36

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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 36

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 36

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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

In this article we have discussed the manual scenarios to recover data from Windows 10 through some backup and recovery tools which are offered by it.
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
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…
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…
Suggested Courses

580 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