# DATE CALCULATIONS USING AS400 QUERY

• 10-05-2007, 02:50 AM
unix
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??

• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
Current(Date) - 30 Days

Birgitta

On 10 Aug., 17:33, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote:[color=blue]
> 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??[/color]

• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
On Fri, 10 Aug 2007 08:33:40 -0700, JIMBO wrote:[color=blue]
> 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??[/color]

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
• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
On Aug 10, 1:04 pm, "Hau...@sss-software.de" <Hau...@sss-software.de>
wrote:[color=blue]
> Current(Date) - 30 Days
>
> Birgitta
>
> On 10 Aug., 17:33, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote:
>
>
>[color=green]
> > 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 -[/color]
>
> - Show quoted text -[/color]

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

• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
On Fri, 10 Aug 2007 12:17:40 -0700, JIMBO wrote:[color=blue]
> On Aug 10, 1:04 pm, "Hau...@sss-software.de" <Hau...@sss-software.de>
> wrote:[color=green]
>> Current(Date) - 30 Days
>>
>> Birgitta
>>
>> On 10 Aug., 17:33, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote:
>>
>>
>>[color=darkred]
>> > 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 -[/color]
>>
>> - Show quoted text -[/color]
>
> 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[/color]

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
• 10-05-2007, 02:50 AM
unix
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:[color=blue]
> 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[/color]
• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
It's even easier with SQL (*QMQRY)

Select Current_Date - 30 Days
....

Birgitta
[color=blue]
> It's not that hard with SQL (*QMQRY).
>
> SELECT DATE(DAYS(CURDATE()) - 30)
>
> will hand back the date 30 days prior.[/color]

• 10-05-2007, 02:50 AM
unix
Re: DATE CALCULATIONS USING AS400 QUERY
On Aug 10, 9:33 am, JIMBO <JST...@SHENANDOAHFURNITURE.COM> wrote:[color=blue]
> 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??[/color]

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