Solved

AS400 Query

Posted on 2011-02-10
6
958 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
Comment Utility
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
Comment Utility

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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:Gary Patterson
Comment Utility
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
Comment Utility
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
Comment Utility
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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

1. Start | Settings | Printers and Faxes. 2. Go to the File Menu. 3. Select Server Properties. 4. Select the Drivers tab. 5. Select the printer you need information about. 6. Once again select Properties. You should get a list of the files requ…
I wrote an article (http://www.experts-exchange.com/articles/2245/Anti-rootkit-software.html) some time ago with a reference to nLite  (http://www.nliteos.com/)slipstreaming software.  I recently changed that link to point to NTLite (https://www.ntl…
This video discusses moving either the default database or any database to a new volume.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now