SQL常用函数

上一篇 / 下一篇  2019-02-09 11:31:57 / 个人分类: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);


TAG:

 

评分:0

我来说两句

显示全部

:loveliness: :handshake :victory: :funk: :time: :kiss: :call: :hug: :lol :'( :Q :L ;P :$ :P :o :@ :D :( :)

Open Toolbar