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

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

LOL Chuck

I keep forgetting about the implicit CAST - maybe because I don't 
develop on a release that allows it. I probably wouldn't use it, in any 
case, as I like to avoid too many "cute" techniques and be a little more 
explicit in what is happening.

But that's just me on a good day!!

Good Day!!
Vern

CRPence wrote:
>    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>>
> > >