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.
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.
use the keyword DATFMT(*USA) in the dspec during ur date field declaration.
- 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 :- – +
- <pre> +
- Field Expression Column Heading Len Dec +
- DDMMYYYY SUBSTR(DIGITS(DATFLD), 7, 2)||’/’ +
- ||SUBSTR(DIGITS(DATFLD), 5, 2)|| +
- ‘/’||SUBSTR(DIGITS(DATFLD), 1, 4) +
- </pre> +
- Regards, +
- Martin Gilbert.
It’s a date type and you want to format it *USA – for what purpose? Is the result another date field?
D DateUSA D datfmt(*USA)
D DateISO D datfmt(*ISO)
Eval DateUSA = DateISO
I thought this was a question about Query?
Disregard my answer… not a valid response to your need.