Re: SQL Date Range Problem with Between

First of all, both queries are NOT valid BETWEEN statements.
Each BETWEEN is it's own condition, and since BETWEENs work on a FROM and
TO condition, nothing could ever come between 14 and 10 (as peformed in
the 2nd statement).

Try NOT separating the values and performing the BETWEEN as (CAL BETWEEN
20090714 and 20090810).


David L. Mosley, Jr.
Technical Solutions Architect
Dancik International, Ltd.
2000 CentreGreen Way, Suite 250
Cary, NC 27513

www.dancik.com



Chandana Silva <chandana.silva@xxxxxxxxxxxxx>
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
01/30/2010 10:15 AM
Please respond to
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>


To
RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx>
cc

Subject
SQL Date Range Problem with Between






Hello,

I am sure someone has hit this bottleneck before with the BETWEEN
operator.
My search online was not successful.

Objective is to get a total for a date range within different
months/years.

This statement works fine, for the same month.

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY BETWEEN 2009 And 2009 AND
CALM BETWEEN 07 And 07 AND : This is in July
CALD BETWEEN 14 And 31;

This statement Returns zero if the CALD is less than 14. But I haven't
verified if value is correct even if the CALD > 14.

Select sum(SALES)
From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY BETWEEN 2009 And 2009 AND
CALM BETWEEN 07 And 08 AND : This is from July thr Aug
10
CALD BETWEEN 14 And 10;

Any help is greatly appreciated.