2

I have came through a query in SQL which will convert number we enter to word format The Query is 

select to_char(to_date(&num,'J'),'JSP') from dual;

 TO_CHAR(TO_DATE(678,'J'),'JSP')
 -------------------------------
 SIX HUNDRED SEVENTY-EIGHT

Normally the Julian Date Format gives the number of days added to the Julian date i.e 31-DEC-4712 I am unable to understand how does it convert every number into words.

Help me in understanding what it does actually ...

Jim
  • 6,753
  • 12
  • 44
  • 72
Aspirant
  • 2,238
  • 9
  • 31
  • 43

1 Answers1

5

The to_date function converting the date to julian is not responsible for the result coming up in plain english words. That is the to_char function with 'JSP' as an argument.

I'd explain, but really I think the explanations at the links below can explain better than I could.

Here is a good Ask Tom post that explains Julian Date.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1406145293951

And here is another good Ask Tom post that explains how to_char with 'JSP' will give you the number in english words.

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:18305103094123

Jim
  • 6,753
  • 12
  • 44
  • 72
  • 1
    Note that the Oracle 12 documentation does not document the "JSP" (or "Jsp" for Mixed Case) format element, so bookmark this answer! Also, Ask Tom has a nice function that will spell out numbers greater than 5373484, which is the max the Jsp can handle: https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1407603857650 – Mark Stewart May 05 '16 at 14:04