RE: SQL Date Math

Three things Elvis...

1) I already mentioned the performance issue ;-)
2) You can't CAST() from Date to Numeric
3) The UDF shown was for the date to numeric conversion, to resolve issues 1 & 2

(Just didn't want to give somebody reading the archives the wrong idea about my UDF <grin>)

Charles

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Tuesday, November 13, 2007 2:55 PM
To: 'Midrange Systems Technical Discussion'
Subject: 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

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

This e-mail transmission contains information that is intended to be confidential and privileged. If you receive this e-mail and you are not a named addressee you are hereby notified that you are not authorized to read, print, retain, copy or disseminate this communication without the consent of the sender and that doing so is prohibited and may be unlawful. Please reply to the message immediately by informing the sender that the message was misdirected. After replying, please delete and otherwise erase it and any attachments from your computer system. Your assistance in correcting this error is appreciated.