AS/400 Query – Timestamp of entry to char field
Hi friends,
I am creating an account file with the command below:
DSPJRN JRN(QSYS/QACGJRN) FROMTIME('26/09/2009') TOTIME('25/10/2009') ENTTYP(SP) OUTPUT(*OUTFILE) OUTFILFMT(*TYPE4) OUTFILE(QTEMP/QAPTACG4)
How can I convert the Timestamp field called JATSTP to a new char field with the first four positions ?
Define Result Fields Type definitions using field names or constants and operators, press Enter. Operators: +, -, *, /, SUBSTR, ||, DATE... Field Expression Column Heading Len Dec YYYY SUBSTR(digits(Year(jatstp)),1,4) Bottom Field Text Len Dec T01.JATSTP Timestamp of entry 26 Z T01.JAJOBH Job name 10 T01.JAUSRH User name 10 T01.JANBRH Job number 6 0
The result being that
Display Report Report width . . . . . : 551 Position to line . . . . . Shift to column . . . . . . Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10....+...11....+...12.... YYYY TIMESTAMP JOB JOB JOB USER ENTRY SEQUENCE CODE TYPE PROGRAM NAME USER NUMBER PROFILE LENGTH NUMBER NAME 000001 0000 2009-11-11-06.14.32.885296 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 201 A SP QCMD 000002 0000 2009-11-11-06.16.16.529072 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 243 A SP QCMD 000003 0000 2009-11-11-06.16.45.244752 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 244 A SP QCMD 000004 0000 2009-11-11-06.17.38.368864 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 245 A SP QCMD 000005 0000 2009-11-11-06.17.39.987744 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 246 A SP QCMD 000006 0000 2009-11-11-06.19.39.597904 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 248 A SP QCMD 000007 0000 2009-11-11-06.20.22.982576 PRT_LABEL QSPLJOB 602,868 QSPLJOB 394 250 A SP QCMD 000008 0000 2009-11-11-06.30.55.433584 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 253 A SP QCMD 000009 0000 2009-11-11-06.30.57.741632 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 254 A SP QCMD 000010 0000 2009-11-11-06.31.00.686416 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 258 A SP QCMD 000011 0000 2009-11-11-06.31.03.232528 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 259 A SP QCMD 000012 0000 2009-11-11-06.31.05.402176 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 260 A SP QCMD 000013 0000 2009-11-11-06.31.10.295088 BARCODE QSPLJOB 602,975 QSPLJOB 394 264 A SP QCMD 000014 0000 2009-11-11-06.31.25.256640 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 265 A SP QCMD 000015 0000 2009-11-11-06.31.30.835712 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 266 A SP QCMD 000016 0000 2009-11-11-06.31.41.231040 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 267 A SP QCMD 000017 0000 2009-11-11-06.31.42.975456 PRT_PICK QSPLJOB 602,971 QSPLJOB 394 268 A SP QCMD
Thanks for all
Software/Hardware used:
ASKED:
December 8, 2009 11:29 PM
UPDATED:
December 14, 2009 12:22 PM
RELATED QUESTIONS
- Using Oracle E-Business Suite with Lotus Domino
- AS/400 backup tape disaster
- Sudden SQL errors keep Business Contact Manager from loadingHELP - Sudden SQL errors keep Business Contact Manager from loading
- Restoring Journalized Files
- Returning string value from a stored procedure in sql server 2005 to vc# ide code at runtime
Answer Wiki:
If you are using AS400 query, under define fields, try SUBSTR(DIGITS(JATSTP,1,4).
Thats missing a ) as in SUBSTR(DIGITS(JATSTP),1,4)
or perhaps
SUBSTR(CHAR(JATSTP),1,4)
Phil.
To see all answers submitted to the Answer Wiki: View Answer History.
How can I convert the Timestamp field called JATSTP to a new char field with the first four positions ?
Sorry, but was there supposed to be something else at the end of that question?
JATSTP is just a database timestamp field. You manipulate like any other field of that type. Can you provide an example of what you’d like the result to look like?
Also, please avoid putting comments inside of code-blocks, especially with code lines that don’t have end-of-line characters that this editor understands. (I think it likes Windows CR/LF. Maybe it can’t handle simple LF terminators…?)
It’s difficult to separate sections and make sense of everything.
Thanks!
Tom
BTW, that looks like a Query/400 screen. It’s been many years since I’ve looked at those. If that’s what it is, that’s probably the biggest part of the problem. You should have dropped Query/400 more than a decade ago. It’d be easier to tell you how to work with the field in a QM query.
Tom
Thanks friends, Phil your example works.
Merry Christmas and happy new Year to all.
Roberto.