Re: Keeping track for SPs, UDFs and service programs/procedures...

On 17-Nov-2011 05:33 , rob@xxxxxxxxx wrote:

<<SNIP>>

CREATE FUNCTION ROUTINES.DATETONUM ( REALDATE DATE )
RETURNS DECIMAL(8, 0)
LANGUAGE SQL
<<SNIP>>
BEGIN
DECLARE NUMDATE DECIMAL ( 8 , 0 ) ;
SET NUMDATE = INT(
YEAR ( REALDATE )
|| RIGHT ( '0' || MONTH ( REALDATE ) , 2 )
|| RIGHT ( '0' || DAY ( REALDATE ) , 2 )
) ;
RETURN ( NUMDATE ) ;
END ;

<<SNIP>>

OT: FWiW, the following rewrite of that SQL UDF rids of the concat symbols, the breaking down of the date components, those components reconstituting the composite date using the concatenation, and the intermediate variable for which BEGIN\END are required; returning the numeric string of digits implicitly cast to the declared return-as data type with just one reference to the input parameter in the expression with just two scalar functions:

CREATE FUNCTION ROUTINES.DATETONUM ( REALDATE DATE )
RETURNS DECIMAL(8, 0)
LANGUAGE SQL
<<SNIP>>
RETURN REPLACE( CHAR( REALDATE, ISO ) , '-', '' ) ;

I do not _know_ which would be faster, but I would guess the latter.

Regards, Chuck