Re: AW: SQL Date Range Problem with Between - Resolved

FWiW the implicit CAST by the SQL should enable the statement to function in most typical cases; I would still recommend correcting the expression however. If the CALY is a numeric with zero scale, and the year values would always be year 1000 or greater, then the statement should function well; albeit possibly flawed for years 1 to 999, depending on how the BETWEEN values are formed. That is because the CALY will be treated implicitly as VARCHAR(CALY) in the expression concatenating the other character strings resulting from both of the the DIGITS scalar; i.e. VARCHAR(DECIMAL(2009, 6, 0)) = '2009' is little different from DIGITS(DECIMAL(CALY, 4, 0)) for when CALY=2009.

Regards, Chuck

Vern Hamberg wrote:

Is CALY a numeric variable? Then this is still not working
- you must have left out something.

<<SNIP>>

Chandana Silva wrote:

<<SNIP>>

I want to share the final result, which is nice and clean.

Select sum(SALES)
From DaySALES
WHERE CPY = 22 and STR = 1234
AND CALY || DIGITS(CALM) || DIGITS(CALD)
BETWEEN '20090714' AND '20090810';

I am using host values for the BETWEEN :FrmDate And :Todate
and in cpy and str, in my actual code.
Performance : Statement ran successfully (31 ms) - via
iseries navigator

<<SNIP>>