RE: SQL Date Math

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;