ORA-01830 when converting number to words

High value is in decimal format eg.- 100.10, I want to convert it into word so I write below script but not getting execution by this..

SELECT SYMBOL, HIGH, UPPER(TO_CHAR(TO_DATE(HIGH,'J'),'JSP'))
AMT_IN_WORDS FROM BHAV; 

getting error of

ORA-01830

please correct this where am wrong....

Thank you in advance...

1 answer

  • answered 2018-04-14 14:29 Kaushik Nayak

    The error is raised since the value of high that you have shown is a decimal, that cannot be cast as an integer implicitly, unlike 100.00. So, it cannot be converted to Julian date.

    SELECT  UPPER(TO_CHAR(TO_DATE(100.10,'J'),'JSP'))AMT_IN_WORDS FROM DUAL;
    

    This causes

    ORA-01830: date format picture ends before converting entire input string

    This can be resolved by rounding the decimal to the nearest integer.

    SELECT  UPPER(TO_CHAR(TO_DATE(ROUND(100.10),'J'),'JSP'))AMT_IN_WORDS FROM DUAL;
    
    | AMT_IN_WORDS |
    |--------------|
    |  ONE HUNDRED |
    

    Demo

    If you really want the float component as well, although limited, you may refer this answer's EDIT2: How to convert number to words - ORACLE