oracle 几个函数的应用

引子：
字符串转为数字用to_number函数，例如
to_number('1210.73')就得到1210.73

数字转为字符串用to_char函数，例如
to_char(1210.73)就得到'1210.73'

两个函数都可以设定转换的格式，例如
to_char(1210.73, '$9,999.00')就得到'$1,210.73'

例子：当小时出现大于或等于24时，用to_date去格式化它，会出错。下面给出一种做法
//sasp中的小时数会出现24非法数据(0~23是正常数据)
select aa, odate, otime, trunc((to_date(odate||otime,'YYYYmmddhh24mi')-to_date('200811221122','YYYYmmddhh24mi'))*24*60)
from ele01_sasp
where
abs(trunc((to_date(odate||otime,'YYYYmmddhh24mi')-to_date('200811221122','YYYYmmddhh24mi'))*24*60))<15
and to_number(substr(otime, 1, 2))<24
and tt='SA'
再union一个otime>23的即可。
//下面可以将2400正确格式化
select to_char((to_date('200811220000','YYYYmmddhh24mi') + (to_number(substr('2400', 1, 2)) + to_number(substr('2400', 3, 2))/60)/24), 'YYYYmmddhh24mi')
from dual
合并后的sql为
    select aa, odate, otime, trunc((to_date(odate||otime,'YYYYmmddhh24mi')-to_date('200811221122','YYYYmmddhh24mi'))*24*60)
    from ele01_sasp
    where
    abs(trunc((to_date(odate||otime,'YYYYmmddhh24mi')-to_date('200811221122','YYYYmmddhh24mi'))*24*60))<15
    and to_number(substr(otime, 1, 2))<24
UNION
    select aa, odate, otime, trunc(((to_date(odate,'YYYYmmdd') + (to_number(substr('2400', 1, 2)) + to_number(substr('2400', 3, 2))/60)/24)-to_date('200811221122','YYYYmmddhh24mi'))*24*60)
    from ele01_sasp
    where
    abs(trunc(((to_date(odate,'YYYYmmdd') + (to_number(substr('2400', 1, 2)) + to_number(substr('2400', 3, 2))/60)/24)-to_date('200811221122','YYYYmmddhh24mi'))*24*60))<15
    and
    to_number(substr(otime, 1, 2))=24