Vijay

• Aug 10th, 2005

 

select sal, (to_char(to_date(sal,'j'), 'jsp'))  
from emp;

nagesh

• Mar 10th, 2006

 

Thank you.This has helped me a lot. But can any one explain how it works?

Thank you

Nagesh

deva

• Sep 20th, 2006

 

thanks vijay,but deadly waiting for the explanation....

once again thanks for the good one.....

manideepika

• Oct 17th, 2006

 

The answer worked out...but can i get an explaination for this answer?

thanks

adarsh_sp

 Profile Answers by adarsh_sp Questions by adarsh_sp

• Oct 24th, 2006

 

This is using ' julian' functionHere 'j' used as a format in to_date function return the number in julian year.Whereas the 'jsp' used in to_char made the julian year spelled out.i.e. j-julian year and sp-spelling so it is ' jsp'. there is some limitations we cac use this only in numbers from 1 -5373484.I think we cannot retrive numbers in words using a single querry other than julian option....ie,within the range 1 -5373484. but we can create a function for this conversion ...If any one knows another way of conversion using querry other than julian plz send me...

Guest

• Oct 24th, 2006

 

This is using ' julian' functionHere 'j' used as a format in to_date function return the number in julian year.Whereas the 'jsp' used in to_char made the julian year spelled out.i.e. j-julian year and sp-spelling so it is ' jsp'. there is some limitations we cac use this only in numbers from 1 -5373484.I think we cannot retrive numbers in words using a single querry other than julian option....ie,within the range 1 -5373484. but we can create a function for this conversion ...If any one knows another way of conversion using querry other than julian plz send me...

Shruti Toggi

• Sep 11th, 2007

 

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

eg:

SQL>  select to_char(to_date(123,'j'),'jsp') from  dual;

TO_CHAR(TO_DATE(123,'J')
------------------------
one hundred twenty-three

bhaskar1981buie

 Profile Answers by bhaskar1981buie 

• Dec 4th, 2007

 

The number which have precesion can be spelled in words by this way..

SELECT DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),

0,11,DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),1,4),

'0000','',TO_CHAR(TO_DATE('01-12-'||SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),

0,11,DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),1,4),'DD-MM-YYYY'),

'YYYYSP')||' CRORE ')||

DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,

DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),5,2),'00','',

TO_CHAR(TO_DATE('01-01-'||SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),

0,11,DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),5,2),'DD-MM-YY'),

'YYSP')||' LAKH ')||

DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,

DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),7,2)

,'00','',TO_CHAR(TO_DATE('01-01'||SUBSTR(LPAD('1256932.1',

DECODE(INSTR('1256932.1','.'),0,11,DECODE(LENGTH('1256932.1')-

INSTR('1256932.1','.'),1,13,14)),'0'),7,2),'DD-MM-YY'),'YYSP')||' THOUSAND ')||

DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,

DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),9,1),'0',''

,TO_CHAR(TO_DATE('01-'||SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),

0,11,DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),

9,1)||'-2006','DD-MM-YYYY'),'MMSP')||' HUNDREAD ')||

DECODE(SUBSTR(LPAD('1256932.1',DECODE(INSTR('1256932.1','.'),0,11,

DECODE(LENGTH('1256932.1')-INSTR('1256932.1','.'),1,13,14)),'0'),10,2),

'00','',TO_CHAR(TO_DATE('01-01'||SUBSTR(LPAD('1256932.1',

DECODE(INSTR('1256932.1','.'),0,11,DECODE(LENGTH('1256932.1')-

INSTR('1256932.1','.'),1,13,14)),'0'),10,2),'DD-MM-YY'),'YYSP')||' RUPPES ')||

DECODE(INSTR('1256932.1','.'),0,'',' AND '||TO_CHAR(TO_DATE('01-01-'||

RPAD(SUBSTR('1256932.1',INSTR('1256932.1','.')+1,2),2,'0'),'DD-MM-YY'),'YYSP')||

' PAISE ONLY')

FROM DUAL

emraan

 Profile Answers by emraan 

• Dec 29th, 2009

 

select sal, to_char(to_date(sal, 'j'), 'jsp') from emp