AS/400 Query – Timestamp of entry to char field

550 pts.
Tags:
AS/400
AS/400 Query


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

 

 

 

 

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • TomLiotta
    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
    125,585 pointsBadges:
    report
  • RBDSILVA
    Thanks friends, Phil your example works. Merry Christmas and happy new Year to all. Roberto.
    550 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following