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??...

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??

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??

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

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

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

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

Re: DATE CALCULATIONS USING AS400 QUERY

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.