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

[CRPence <CRPbottle@xxxxxxxxx>]

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