Re: SQL Date Range Problem with Between

You need to concatinate the fields to create a full date, yyyymmdd works fine. The problem you have is when they are broken apart, you are searching for three numeric field ranges.

--
Mike Wills
http://mikewills.info
P: (507) 933-0880 | Skype: koldark

Sent from my iPod

On Jan 30, 2010, at 9:15 AM, Chandana Silva <chandana.silva@xxxxxxxxxxxxx > wrote:

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