Re: difference between two dates in SQL

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.