JSP Format Model

To convert date into numbers and words

select to_char(sysdate,'J') "WORDS" from dual;
select to_date(to_char(sysdate,'J'),'J') "WORDS" from dual;
select to_char(to_date( to_char(sysdate,'J') ,'J'), 'JSP') "WORDS" from dual;

----*--------------------------------*-----------------------
To convert numbers into words

select to_char(to_date(873,'J'), 'JSP') as converted_form from dual;

------*-------------------------------------*------------------------
To convert words into numbers


SELECT LEVEL wordasint FROM dual
WHERE TO_CHAR(TO_DATE(LEVEL,'J'), 'JSP') = 'ONE HUNDRED TWENTY-THREE'
CONNECT BY TO_CHAR(TO_DATE(LEVEL-1,'J'),'JSP') != 'ONE HUNDRED TWENTY-THREE'
AND LEVEL < 124; ---FOR 10 g and above
SELECT sp, n
FROM (SELECT 'EIGHTY-SIX THOUSAND THREE HUNDRED NINETY-NINE' sp FROM dual)
MODEL DIMENSION BY (1 dim)
MEASURES (0 n, sp) RULES ITERATE (86400) UNTIL (TO_CHAR(DATE '0001-01-01' +
(ITERATION_NUMBER/86400),'SSSSSSP')=sp[1])
(n[1]=ITERATION_NUMBER);

------------------------------------------------------------------------