Search code examples
sqloracletype-conversionto-dateto-char

how to display number value in words

Q. Display the number value in Words and output should look like this

SAL        In_Words
--------- -----------------------------------------------------
800       eight hundred
1600      one thousand six hundred
1250      one thousand two hundred fifty

And, I'm still didn't figure out, how this query is the solution for the above output.

select sal, to_char(to_date(sal,'j'),'Jsp') in_words from emp

What to_date is doing here ? Anyone have any idea about this query ?

Solution

  • So how the query works? Well here’s why:

    select to_char(to_date(:number,'j'),'jsp') from dual;

    If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.

    So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.

    If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.

    select to_char(to_date(3,'j'),'jsp') from dual;

    Now to_char(to_date(3,'j'),'jsp'), jsp = Now; take that date(to_date(3,'j')) and spell the julian number it represents, the output is:

    TO_CH
-----
three

    There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

    ORA-01854: julian date must be between 1 and 5373484

    Hi everyone, it is interesting this topic. I remember when I was learning Oracle in 2005 one of the instructor required me to write a PL/SQL code to convert numbers in words, it was a whole two pages code to reach this.

    Here is some reference that could help us to understand the Julian day, that is why we use the letter 'j' or 'J' during this operation.

    First there is a website that has the example and explanation about "How To Convert Number Into Words Using Oracle SQL Query":

    http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/

    Second if you want to know more about "Julian day" go to:

    http://en.wikipedia.org/wiki/Julian_day

    Third if you want to know more about who proposed the Julian day number in 1583, it was by "Joseph Scaliger":

    http://en.wikipedia.org/wiki/Joseph_Justus_Scaliger

    It is not make sence for me continue repiting what another author in these websites have made, that is why I just posted the link you can access them and read what you need to understand how query like this works:

    SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;

    //Output: two million four hundred forty-seven thousand eight hundred thirty-four