• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 982
  • Last Modified:

AS400 Query

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
gwickers
Asked:
gwickers
  • 3
  • 2
1 Solution
 
MurpheyApplication ConsultantCommented:
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
 
_b_hCommented:

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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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 Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Gary PattersonVP Technology / Senior Consultant Commented:
Oops!  Barry must've posted while I was still typing!  

Points to him and not me, please.
0
 
MurpheyApplication ConsultantCommented:
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
 
Gary PattersonVP Technology / Senior Consultant Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now