Re: difference between two dates in SQL

[Charles Wilt <charles.wilt@xxxxxxxxx>]

> From the manual:

Date duration
 A date duration represents a number of years, months, and days,
expressed as a DECIMAL(8,0) number. To be properly interpreted, the
number must have the format yyyymmdd, where yyyy represents the number
of years, mm the number of months, and dd the number of days. The
result of subtracting one date value from another, as in the
expression HIREDATE - BRTHDATE, is a date duration.

Assuming that you're using 30 days = 1 month, you might what to
compare to > 100 and < 200.

Though honestly, I don't know what the max number of days that can be
encoded in a returned date duration is.

What I usually do is compare two dates instead:
select * from myfile where :wtoday > (ssodat + 30 days)
                               and :wtoday < ( ssodat + 61 days )

HTH,
Charles


On Thu, Dec 17, 2009 at 4:21 PM, Doug Palme <DPalme@xxxxxxxxxxx> wrote:
> I thought I was doing this right, but I am getting weird results so I
> thought I would quickly ask the group.
>
> I am passing in today's date as a var (date data type) which I am using to
> compare against a date field.
>
> My sql statement is as follows:
>
> select * from myfile where (:wtoday - ssodat) > 30 and (:wtoday - ssodat)
> < 61;
>
> I thought that substracting two dates from each other would return a
> result in days is this not correct?
>
> If not, any suggestions on how to accomplish this?
>
> Douglas
>
>
> --
> This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.