Re: SQL Date Range Problem with Between

Hi Chandana

Well, your second one won't work, because BETWEEN is the same as CALD >= 14 and CALD <= 10 - there is nothing that fits that criteria.

It is never reliable or simple to compare multiple values with ranges - you end up with VERY complex conditions. I mean, you'd require multiple BETWEENs for the second one - the first would be from 14 to the last day of the month - which is not the same always, so you have to account for that plus leap years - well, you see what I mean!!

I think you really need to either convert your numbers to character and concatenate to a single value. You probably need to use the DIGITS function if these will come from columns. I assume that CALY is 4,0 and CALM and CALD are both 2,0 - DIGITS() preserves leading zeroes, which you must have here.

Select sum(SALES) From Daysales WHERE CPY = 22 and STR = 1234 AND
digits(CALY) concat digits(calm) concat digits(cald) BETWEEN '20090714' And '20090810';

Or use multiplication

Select sum(SALES) From Daysales WHERE CPY = 22 and STR = 1234 AND
CALY * 10000 = calm * 100 + cald BETWEEN 20090714 And 20090810;

Mulitiplication tends to take more cycles than concatenation, I think, but maybe it doesn't matter these days.

HTH
Vern
Chandana Silva 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.