SQL常用函数

--换算单位并保存两位小数

 select to_char(crlim/100,'9999999999999990.99') as crlim from A where cust='0000000610079545330' and rg=242

--获取当前日期

 select to_char(sysdate,'YYYY-MM-DD') from dual

--查看数据库空间

select b.NAME, sum(a.BYTES/1024/1024/1024) from v$datafile a, v$tablespace b where a.TS#=b.TS# group by b.NAME

---联机时间转换

to_date(substr(to_char(a.date_online),0,13),'yyyydddhh24miss')

--日期范围查询

select a.date_created from ot_data_amb a where a.date_created between to_date('20140901','YYYYmmDD') and to_date('20150901','YYYYmmDD')

--查询date类型的day、month、year是符合条件的数据

SELECT    DATE_LAST_STMT     FROM      OT_DATA_AMB WHERE  extract( DAY FROM  DATE_LAST_STMT)  IN 11

SELECT    DATE_LAST_STMT     FROM      OT_DATA_AMB WHERE  extract( MONTH FROM  DATE_LAST_STMT)  IN 11

SELECT    DATE_LAST_STMT     FROM      OT_DATA_AMB WHERE  extract( YEAR FROM  DATE_LAST_STMT)  IN 11

select cust_nbr,billing_cycle,acct,date_last_cycle from ot_data_amb where  block_code_1=' ' and block_code_2=' ' and acct in (select post_to_acct from ot_data_amed where block_code=' ' and curr_first_usage_flag='N') and extract(day from date_last_cycle) not in (11,12,29,30,31) and acct in (select account_no from ot_data_t_bill_tran_info  )

--四舍五入函数 ROUND()

select ROUND(3.1415926,4) from dual; --3.1416

--截取函数TRUNC

select TRUNC(3.1415926,4) from dual;--3.1415

--FLOOR对指定数字取整数位

select FLOOR(3.1415926) from dual;--3

--CEIL返回大于或等于给出数字的最小整数

select CEIL(3.1415926) from dual; --4

--根据账单日推送还款日

select case 

       when (extract(day from last_cycle ) in(1,2,3,4,5,6,7,8,9,10)) then last_cycle+18 

       else (extrace(day from last_cycle) in(13,14,15,14,17,18,19,20,21,22,23,24,25,26,27,28))  then last_cycle -12  

       end as repay_day 

       from ot_data_amb  where extract(day from last_cycle ) not in (11,12,29,30,31);