Q
Problem solve Get help with specific problems with your technologies, process and projects.

Using Julian date to convert numbers to words

How can I use Julian date to convert numbers to words?

Luckily, the TO_CHAR function can assist you. For instance, assume that I have the number '123' and I want to convert it to a word. Then a SQL statement such as the following will do the trick:

ORA9I SQL> select
to_char(to_date('01-01-0123','MM-DD-YYYY'),'YEAR')
from dual;

TO_CHAR(TO_DATE(
----------------
ONE TWENTY-THREE
In this case, I put the number '123' in the four digit year. I then used the YEAR format mask to spell out the year in words. Here are a few more examples:
ORA9I SQL> select
to_char(to_date('01-01-0456','MM-DD-YYYY'),'YEAR')
from dual;

TO_CHAR(TO_DAT
--------------
FOUR FIFTY-SIX

ORA9I SQL> select
to_char(to_date('01-01-1234','MM-DD-YYYY'),'YEAR')
from dual;

TO_CHAR(TO_DATE('0
------------------
TWELVE THIRTY-FOUR

ORA9I SQL> select
to_char(to_date('01-01-4567','MM-DD-YYYY'),'YEAR')
from dual;

TO_CHAR(TO_DATE('01-01
----------------------
FORTY-FIVE SIXTY-SEVEN
As you can see, the numbers are not spelled out exactly as you want. If you want '4567' to be spelled out as FOUR THOUSAND FIVE HUNDRED SIXTY SEVEN, then the example gets more complicated. And you'll have to write a bit of code to get the answer as you want. Luckily, Tom Kyte has already answered this question on his AskTom web site. The URL for his solution is: http://asktom.oracle.com/pls/ask/f?p=4950:8:249493602386220015::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1407603857650,.

For More Information


Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close