sql> select to_char(to_date('12345','j'),'jsp') en from dual;
en
----------------------------------------
twelve thousand three hundred forty-five
不过有限制:一是长度的限制,二是不能转换带小数的
sql> select to_char(to_date('88888882345','j'),'jsp') from dual;
select to_char(to_date('88888882345','j'),'jsp') from dual
ora-01854: julian 日期必须介于 1 和 5373484 之间
julian date指的是公元前4712年1月1日起经过的天数.
the inner to_char simply converts the number
(which would probably be a numeric variable in practice)
to char so some magic can happen ...
the to_date converts the char using the j (julian day)
format. (the julian day is the number of days since
january 1, 4712bc, which is when sql*plus was invented),
having established the date value, we then convert that
date back to a julian day. because the to_char in this
case is used in date context, we can use the j mask to
duplicate the original value, and append the sp (spell)
format mask. 'spell" does exactly that - it converts the
number to words, hence the string value above.
sp can be used in a number of situations. for example,
if sysdate is 26-aug-98, then :
select to_char ( sysdate, 'ddsp') from dual; -- spells
the day as twenty-six,
and
select to_char ( sysdate, 'ddspth') from dual;
--returns twenty-sixth
|