Re: SQL function return value

In article <94s5pl$rib$1_at_nnrp1.deja.com>,   derf23456_at_my-deja.com wrote:
> I use "To_Char ( ??? , 'DD-Mon-YYYY HH24:MI:SS' )" a lot and, being
> lazy, created a function:
>
> Create or Replace function Long_Date ( In_Date Date )
> Return Varchar2
> Is
> Begin
> Return ( To_Char ( In_Date , 'DD-Mon-YYYY HH24:MI:SS' ) ) ;
> End Long_Date ;
> /
>
> I'd expected a 20 character string as the return value but it seems
> that I'm getting a string with a length equal to my SQL*Plus setting
> for LineSize.
>
> "Select Long_Date ( SysDate ) , Dummy From Dual ;" returns:
>
> LONG_DATE(SYSDATE)
> ----------------------------------------------------------------------
 --
> D
> -
> 25-Jan-2001 08:09:15
> X
>
> (Using "SubStr ( To_Char ( In_Date , ...) , 1 , 20 )" in the function
> doesn't change the return value.)
>
> How can I get only the "proper" length from my select?
>

Try formatting the column in sqlplus
column return_date format a20

select long_date(sysdate) as return_date from dual;

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/