Yes , this question is regarding using iSeries Query option to format a date field. The field length is somehow 7 e.g 2,021,001 means 2002/10/01 in MM/DD/YYYY format. I cannot figure out how to use the option of "Define Result Field" to format this date field to MM/DD/YYYY format.

Waiting to hear from anyone.

Thank you

Regards,
Zanib


Hi,

Normally 7 long date fields are in the format CYYMMDD, where C=0 means year = 19xx and C=1 means year is 20xx. So 2002/10/01 would be 1021001. To convert this in query you could do the following :-

 Field       Expression                         Column Heading        Len   Dec
 YMD         DATFLD + 19000000                                          8     0
                                                                               
                                                                               
                                                                               
 DDMMYYYY    SUBSTR(DIGITS(YMD), 7, 2)||'/'                                    
             || SUBSTR(DIGITS(YMD), 6, 2)||                                    
             '/'||SUBSTR(DIGITS(YMD), 1, 4)                                    
 

If your date has 2 for the century, then you may need to change this slightly.

Regards,

Martin Gilbert.



Hi,

use the keyword DATFMT(*USA) in the dspec during ur date field declaration.

regards,
Murali.S

/////////////////////
Hi,

- This will depend on how the date is currently formatted, but for an example we'll assume that the date is currently YYYYMMDD format. To convert it to DD/MM/YYYY format :- - +
-

 +  
 - Field Expression Column Heading Len Dec +  
 - DDMMYYYY SUBSTR(DIGITS(DATFLD), 7, 2)||'/'  +  
 - ||SUBSTR(DIGITS(DATFLD), 5, 2)||  +  
 - '/'||SUBSTR(DIGITS(DATFLD), 1, 4)  +  
 - 

+
- +
- Regards, +

- Martin Gilbert.
//////////////////////////////////////////
It's a date type and you want to format it *USA - for what purpose? Is the result another date field?
Display, report,
D DateUSA D datfmt(*USA)
D DateISO D datfmt(*ISO)


Eval DateUSA = DateISO

Phil

/////////////////////////

Guys,

I thought this was a question about Query?

Regards,

Martin Gilbert.
///////////////////////
Phil says
Disregard my answer... not a valid response to your need.
Phil

Please complete the following information:

REGISTER / LOG-IN TO DISCUSS

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Submit your e-mail address below to continue

E-mail: 

E-mail: 

Handle:   

Password: 

Forgot Password?

Create Handle: 

Your handle identifies you in ITKnowledgeExchange. Max 30 char.

Create Password: 

Confirm Password: 

Yes, I agree to the IT Knowledge Exchange  Terms and Conditions.

hidden modal window