RE: SQL Date Math

["Elvis Budimlic" <ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx>]

> From performance standpoint, you're better of casting the DATE so it matches
up with your field's data type (NUMERIC(8,0)).  Reason behind that is that
now you give query optimizer a chance to use an existing index over that
field (if any keyed LF or SQL Index with that field as a key exist).

The other way around (casting numeric to date), you have no prayer of using
an index. If tables are large, you WILL feel the difference.

Now, if you changed the field to DATE data type you wouldn't dwell on this
little factoid    :) 

Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
http://centerfieldtechnology.com/training.asp
 

-----Original Message-----
Subject: Re: SQL Date Math

Very nice...thank you.

On Nov 13, 2007 1:21 PM, Wilt, Charles <WiltC@xxxxxxxxxx> wrote:
> create function CvtDateToNumISO(indte date) returns numeric(8,0)
> language SQL
> not fenced
> deterministic no external action
> specific CvtDateToNumISO
> returns null on null input
> contains SQL
> set option datfmt=*ISO
> begin
>   return( ( year(inDte) * 10000) +
>           ( month(inDte) * 100) +
>           ( day(inDte) ) );
> end;