Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, vsb.

Asked: May 10, 2004 - 11:14 pm UTC

Last updated: August 25, 2020 - 2:41 pm UTC

Version: 9.2.0.

Viewed 50K+ times! This question is

You Asked

To Display the number value in Words

i am using this query i am giving this example

RO2003@BDAPPRD.SGP.HP.COM> select sal, (to_char(to_date(sal,'j'), 'jsp')) from emp;


And the output is

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
----------------------------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
2975 two thousand nine hundred seventy-five
1250 one thousand two hundred fifty
850 eight hundred fifty
450 four hundred fifty
2500 two thousand five hundred
1500 one thousand five hundred
1100 one thousand one hundred
950 nine hundred fifty
3000 three thousand
1300 one thousand three hundred
5000 five thousand

i want to know how is query works

SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))

This part what is this ('J','JSP')

Thanks in advance

and Tom said...

j = julian. take the number represented by sal and pretend it is a julian date, convert it into a date.


jsp = Now, take that date and spell the julian number it represents

Rating

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Developer

A reader, July 11, 2006 - 11:38 am UTC

I like asktom.oracle.com web site. I always can find the solutions of the problem and learned a lot from here.

Thanks

Error running in Report6i

Godwin, May 10, 2008 - 5:29 am UTC

i had this error when i run this in Report6i
ORA-01830: Date format picture ends before converting entire
input string. to_char(to_date(B.CLAMOUNT,'j'),'Jsp') AMOUNT_WORDS
Tom Kyte
May 12, 2008 - 12:16 pm UTC

b.clamout was improperly formatted then.

ops$tkyte%ORA10GR2> select to_char( to_date( '123 junk', 'j' ), 'Jsp' ) from dual;
select to_char( to_date( '123 junk', 'j' ), 'Jsp' ) from dual
                         *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


Nothing to do with reports

everything to do with the fact that you've used a STRING to store a NUMBER - but wait, we all know you cannot do that, when you use a string, the only thing you can store is a string! Therefore, you have no numbers.....


Or you do have a number... but it is not an integer;


ops$tkyte%ORA10GR2> select to_char( to_date( 123.321, 'j' ), 'Jsp' ) from dual;
select to_char( to_date( 123.321, 'j' ), 'Jsp' ) from dual
                         *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string

Its a Number but not an integer

Godwin, May 14, 2008 - 4:23 am UTC

Tom you are right. The Number am trying to convert is not an integer. It contains two decimal places like you've demonstrated. Can't it be a way to convert the decimals as well?
Thanks.
Tom Kyte
May 14, 2008 - 12:56 pm UTC

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

for a better article on spelling numbers

but - just break the number into two bits?? you know, substr or something?


Errous Solution

Tanoy, February 04, 2010 - 3:26 am UTC

Hay This Query Is Not Entirely Correct..It Can Only Convert No Between 1 to 5373484....Any Thing Exceed This Limit Is Not Possible To Convert..
Tom Kyte
February 04, 2010 - 1:56 pm UTC

Hoy

not erroneous - it does what it does... And if you read the link above your comment - you'll see a link to one that does more.


that one I did does this:

ops$tkyte@DEV816> select
  2  spell_number( 12345678901234567890123456789012345678 )
  3  from dual;

SPELL_NUMBER(1234567890123456789012345678901234567
--------------------------------------------------
Twelve undecillion Three Hundred Forty-Five decill
ion Six Hundred Seventy-Eight nonillion Nine Hundr
ed One octillion Two Hundred Thirty-Four septillio
n Five Hundred Sixty-Seven sextillion Eight Hundre
d Ninety quintillion One Hundred Twenty-Three quad
rillion Four Hundred Fifty-Six trillion Seven Hund
red Eighty-Nine billion Twelve million Three Hundr
ed Forty-Five thousand Six Hundred Seventy-Eight

A reader, September 01, 2011 - 10:56 am UTC

hey , i tried this one out in my pl/sql developer but this onw didn't work ! why ??
Tom Kyte
September 01, 2011 - 2:21 pm UTC

gee I don't know, but that is only because "this one didn't work" is hugely vague.

define "did not work"
show your work, show us what you typed in.

Reviewer_sat

sathya, May 19, 2012 - 8:08 am UTC 

I tried giving the same example what you have showed the result is like 


  1   select
  2      spell_number( 12345678901234567890123456789012345678 )
  3*     from dual
SQL> /
    spell_number( 12345678901234567890123456789012345678 )
    *
ERROR at line 2:
ORA-00904: "SPELL_NUMBER": invalid identifier


Can you help why it did not work.. ??

Tom Kyte
May 21, 2012 - 8:21 am UTC

did you create a function named spell_number??

it was referenced above

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

nilesh, July 11, 2014 - 7:21 am UTC

spel1_number function is user define function and it will not applicable if number contains zero value.since to_date julian day conversion of number value applicabale from 1 to certain number..

Error whiel converting

Cajie, August 18, 2016 - 12:21 am UTC

Thanks Tom and team for your efforts, Really Appreciate.

While I was converting, I am getting the date format picture error. Please see the snapshot. Is it something to do with the oracle version or is it a bug? I am able to convert it till 7 digits, 8 digit onward, I am getting the error.

SQL> SELECT TO_CHAR (TO_DATE ('123', 'J'),
  2                  'Jsp')
  3    FROM DUAL
  4  ;

TO_CHAR(TO_DATE('123','J
------------------------
One Hundred Twenty-Three

SQL> SELECT TO_CHAR (TO_DATE ('1234567', 'J'),
  2                  'Jsp')
  3    FROM DUAL
  4  ;

TO_CHAR(TO_DATE('1234567','J'),'JSP')
---------------------------------------------------------------------
One Million Two Hundred Thirty-Four Thousand Five Hundred Sixty-Seven

SQL> SELECT TO_CHAR (TO_DATE ('12345678', 'J'),
  2                  'Jsp')
  3    FROM DUAL
  4  ;
SELECT TO_CHAR (TO_DATE ('12345678', 'J'),
                         *
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string


SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL>



Connor McDonald
August 18, 2016 - 2:35 am UTC

Check out the function in the link

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

It will go to higher values

TO_DATE(,'j') doesn't handle a zero

Spanky, August 21, 2020 - 1:52 pm UTC

Important to note that the TO_DATE function when you are translating a value into julian date format does not handle a value of 0. The values you pass into it are 1 through 5,373,484. If you have a zero you'll have to do a case statement to convert it from 0 to the word zero. Otherwise you will get an ORA-01854 error.
Chris Saxon
August 21, 2020 - 2:13 pm UTC

Yep, there's no year zero in Oracle Database:

with rws as (
  select to_date ( '29-DEC-0001 BC', 'DD-MON-YYYY BC' ) + level d
  from   dual
  connect by level <= 5
)
  select to_char ( d, 'DD-MON-YYYY BC' )
  from   rws;
  
TO_CHAR(D,'DD-MON-YYYYBC')   
30-DEC-0001 BC                
31-DEC-0001 BC                
01-JAN-0001 AD                
02-JAN-0001 AD                
03-JAN-0001 AD

Panic in the year zero

Andrew, August 24, 2020 - 12:08 pm UTC

For me, I get
with rws as (
  select to_date ( '29-DEC-0001 BC', 'DD-MON-YYYY BC' ) + level d
  from   dual
  connect by level <= 5
)
  select to_char ( d, 'DD-MON-YYYY BC' )
  from   rws;

TO_CHAR(D,'DD-MON-YYYYB
-----------------------
30-DEC-0001 BC
31-DEC-0001 BC
00-000-0000 00
00-000-0000 00
00-000-0000 00

To verify with one row, I get

SQL> show parameter NLS_DATE_FORMAT
NAME                                TYPE        VALUE
------------------------------------ ----------- ----------------------
nls_date_format                      string      dd Mon yyyy hh24:mi:ss

SQL> select to_date(  '29-DEC-0001 BC', 'DD-MON-YYYY BC' ) +5 from dual;
TO_DATE('29-DEC-0001
--------------------
03 Jan 0000 00:00:00

or
SQL> select to_char( to_date(  '29-DEC-0001 BC', 'DD-MON-YYYY BC' ) +3,  'DD-MON-YYYY BC') from dual
  2  ;
TO_CHAR(TO_DAT
--------------
00-000-0000 00


Not really Panic in the Year Zero (a pun on the title of a really bad, I know), but quite curious anyway.
Chris Saxon
August 24, 2020 - 2:28 pm UTC

Which version are you running?

I see this in 11.2.0.4, but not 12.2.0.1.

A reader, August 24, 2020 - 9:37 pm UTC

I am seeing it in 12.1.0.2
Chris Saxon
August 25, 2020 - 2:41 pm UTC

If this is causing you problems, contact support to see if there's a patch available.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.