DATE CALCULATIONS USING AS400 QUERY - IBM AS400

This is a discussion on DATE CALCULATIONS USING AS400 QUERY - IBM AS400 ; I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??...

+ Reply to Thread
Results 1 to 8 of 8

Thread: DATE CALCULATIONS USING AS400 QUERY

  1. DATE CALCULATIONS USING AS400 QUERY

    I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??


  2. Re: DATE CALCULATIONS USING AS400 QUERY

    Current(Date) - 30 Days

    Birgitta

    On 10 Aug., 17:33, JIMBO wrote:
    > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??




  3. Re: DATE CALCULATIONS USING AS400 QUERY

    On Fri, 10 Aug 2007 08:33:40 -0700, JIMBO wrote:
    > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??


    It's not that hard with SQL (*QMQRY).

    SELECT DATE(DAYS(CURDATE()) - 30)

    will hand back the date 30 days prior.

    --
    6. I will not gloat over my enemies' predicament before killing them.
    --Peter Anspach's list of things to do as an Evil Overlord

  4. Re: DATE CALCULATIONS USING AS400 QUERY

    On Aug 10, 1:04 pm, "Hau...@sss-software.de"
    wrote:
    > Current(Date) - 30 Days
    >
    > Birgitta
    >
    > On 10 Aug., 17:33, JIMBO wrote:
    >
    >
    >
    > > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    > > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    > > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??- Hide quoted text -

    >
    > - Show quoted text -


    THANKS, I CAN NOW CALCULATE THE DAY I WANT....ANOTHER QUESTION
    NOW....CAN I SEARCH ON THIS? OR HOW CAN I SEARCH A ON THE
    CURRENT(DATE)- 30 DAYS


  5. Re: DATE CALCULATIONS USING AS400 QUERY

    On Fri, 10 Aug 2007 12:17:40 -0700, JIMBO wrote:
    > On Aug 10, 1:04 pm, "Hau...@sss-software.de"
    > wrote:
    >> Current(Date) - 30 Days
    >>
    >> Birgitta
    >>
    >> On 10 Aug., 17:33, JIMBO wrote:
    >>
    >>
    >>
    >> > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    >> > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    >> > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??- Hide quoted text -

    >>
    >> - Show quoted text -

    >
    > THANKS, I CAN NOW CALCULATE THE DAY I WANT....ANOTHER QUESTION
    > NOW....CAN I SEARCH ON THIS? OR HOW CAN I SEARCH A ON THE
    > CURRENT(DATE)- 30 DAYS


    Use it as part of your WHERE clause.

    .... WHERE foofield >= CURRENT(DATE)- 30 DAYS AND ...

    --
    6. I will not gloat over my enemies' predicament before killing them.
    --Peter Anspach's list of things to do as an Evil Overlord

  6. Re: DATE CALCULATIONS USING AS400 QUERY

    Query/400 has the concept of "Result Field" for defining expressions.
    The "Record Selection" is limited to fields compared against literals
    or fields. Thus to perform selection using that expression, first the
    result field is defined as "CURRENT(DATE)-30 DAYS", then the name used
    in defining that result field is used to compare against another field.

    P.S. FWiW "Netiquette" suggests to avoid all upper case in writing,
    because upper case writing is meant to imply SCREAMING. Using mixed
    case also helps the reader to better infer what might be commands,
    acronyms, code, or just conversational text.

    Regards, Chuck
    --
    All comments provided "as is" with no warranties of any kind
    whatsoever and may not represent positions, strategies, nor views of my
    employer

    JIMBO wrote:
    > THANKS, I CAN NOW CALCULATE THE DAY I WANT....ANOTHER QUESTION
    > NOW....CAN I SEARCH ON THIS? OR HOW CAN I SEARCH A ON THE
    > CURRENT(DATE)- 30 DAYS


  7. Re: DATE CALCULATIONS USING AS400 QUERY

    It's even easier with SQL (*QMQRY)

    Select Current_Date - 30 Days
    ....

    Birgitta

    > It's not that hard with SQL (*QMQRY).
    >
    > SELECT DATE(DAYS(CURDATE()) - 30)
    >
    > will hand back the date 30 days prior.



  8. Re: DATE CALCULATIONS USING AS400 QUERY

    On Aug 10, 9:33 am, JIMBO wrote:
    > I NEED TO ACCESS DATA FOR ONLY LAST 30 DAYS IN OUR DATABASE. HOW DO I
    > CREATE A DATE VARIALBLE THAT I CAN USE TO SEARCH DATABASE. I KNOW HOW
    > TO GET CURRENT(DATE), BUT I WANT CURRENT(DATE)-30. ANY HELP??


    Using QRY/400, or SQL (QMQRY)?

    QRY/400;
    You create a result field like

    COMP_DATE Current(date) - 30 days

    To compare you need to have a date data type to compare against. What
    format is your database date in? Already a date data type? Then you
    have it made. If it is in a number that has CYYMMDD where C is a 1 or
    0 for the century, you will have to use result fields to make it into
    a date data type. Basiclly what I do is get the stored date into the
    format that your system defaults to. Mine is MM/DD/YY. Once it is in
    that format, with the separators, then you can use the Date(fieldname)
    function to make your database date into a date data type. Then you
    compare in the select records section YourNewFld GE COMP_DATE. I'm
    don't have QRY/400 in front of me.

    SQL;

    I have installed a free function called iDate that translates most
    date formats into date data types. Then the SQL version might be;
    Where iDate(YourDate) > Current(date) - 30 days.

    HTH

    Jim


+ Reply to Thread