Re: Function needed
Date: Fri, 26 Mar 1999 22:29:19 GMT
Message-ID: <36fc011f.7250119_at_news.siol.net>
[Quoted] On Fri, 26 Mar 1999 11:53:53 +0000, Charles Jardine <cj10_at_cam.ac.uk> wrote:
>Charles Jardine wrote:
>>
>> SQL> edit
>> Wrote file afiedt.buf
>>
>> 1 select to_char(
>> 2 add_months(
>> 3 to_date('00010101','YYYYMMDD'),
>> 4 ((3456-1)*12)
>> 5 ),
>> 6* 'YYYYSP') from dual
>> SQL> /
>>
>> TO_CHAR(ADD_MONTHS(TO_DATE('00010101'
>> -------------------------------------
>> THREE THOUSAND FOUR HUNDRED FIFTY-SIX
>>
>> SQL>
>
>Better is:
>
> 1* select to_char(to_date('1','J') + (5373484 - 1), 'JSP') from dual
Or even simplier:
SCOTT_at_PO73> select to_char(to_date('5373484', 'J'), 'JSP') from dual;
TO_CHAR(TO_DATE('5373484','J'),'JSP')
FIVE MILLION THREE HUNDRED SEVENTY-THREE THOUSAND FOUR HUNDRED EIGHTY-FOUR
>This is the largest number that this method can handle.
[Quoted] Beware hovewer, this 'JSP' (Julian SPelled) conversion alghorytm has some bugs, at least in 7.3.2 (I'm not sure if it was corrected in later releases). It incorectly spells a whole ranges of numbers. For example, any numbers in the ranges of N00001 .. N00999 or in the ranges of NM0001 .. NM0999 (where N is any number from 1 to 9 and M is any number from 2 to 9 will be spelled incorrectly.
An example:
SCOTT_at_PO73> select to_char(to_date('100211', 'J'), 'JSP') from dual;
TO_CHAR(TO_DATE('100211',
ONE HUNDRED THOUSAND TWO So I wouldn't use this technique for let say printing cheques with spelled amount of money ;-)
>--
>Charles Jardine - Computing Service, University of Cambridge
>cj10_at_cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679
HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Mar 26 1999 - 23:29:19 CET