巧用trunc函数，获取某日期范围内的数据

来源：互联网发布：p2p网贷数据编辑：程序博客网时间：2024/04/30 06:19

http://blog.itpub.net/21251711/viewspace-1102673/

先看trunc函数日期的用法：

select trunc(sysdate) from dual; --2014/3/5 今天的日期为2011-3-18

select trunc(sysdate, 'mm') from dual; --2014/3/1 返回当月第一天.

select trunc(sysdate, 'yy') from dual; --2014/1/1 返回当年第一天

select trunc(sysdate, 'dd') from dual; --2014/3/5 返回当前年月日

select trunc(sysdate, 'yyyy') from dual; --2014/1/1 返回当年第一天

select trunc(sysdate, 'd') from dual; --2014/3/2 (星期天)返回当前星期的第一天

select trunc(sysdate, 'hh') from dual; --2014/3/5 10:00:00 当前时间为14:41

select trunc(sysdate, 'mi') from dual; --2014/3/5 10:57:00。

例：表t1有如下数据

SQL> select *from t1 ;

YYYYMMDD NAME

---------- --------

20140201 LIU

20140205 YANG

20140227 MA

20140228 Li

20140301 WU

20140302 WANG

20140303 DONG

需求：根据入参yyyymmdd日期字段做查询判断，若yyyymmdd为当月的前三天（即dd<3)则查询上月至当天的数据，否则查询当月的所有数据。

求解代码如下：

SQL> with t1 as (select to_date('20140201','yyyymmdd') yyyymmdd, 'LIU' name from dual

2 union select to_date('20140205','yyyymmdd') yyyymmdd, 'YANG' name from dual

3 union select to_date('20140227','yyyymmdd') yyyymmdd, 'MA' name from dual

4 union select to_date('20140228','yyyymmdd') yyyymmdd, 'LI' name from dual

5 union select to_date('20140301','yyyymmdd') yyyymmdd, 'WU' name from dual

6 union select to_date('20140302','yyyymmdd') yyyymmdd, 'WANG' name from dual

7 union select to_date('20140303','yyyymmdd') yyyymmdd, 'DONG' name from dual

8 )

9 select * from t1 where yyyymmdd between case when to_char(&&v_date,'dd')<'03' then

10 trunc(add_months(&v_date,-1),'mm') else trunc(&v_date,'mm') end

11 and case when to_char(&v_date,'dd')<'03' then trunc(&v_date,'dd')

12 else trunc(last_day(&v_date),'dd') end

13 /

输入 v_date 的值: sysdate

原值 9: select * from t1 where yyyymmdd between case when to_char(&&v_date,'dd')<'03' then

新值 9: select * from t1 where yyyymmdd between case when to_char(sysdate,'dd')<'03' then

原值 10: trunc(add_months(&v_date,-1),'mm') else trunc(&v_date,'mm') end

新值 10: trunc(add_months(sysdate,-1),'mm') else trunc(sysdate,'mm') end

原值 11: and case when to_char(&v_date,'dd')<'03' then trunc(&v_date,'dd')

新值 11: and case when to_char(sysdate,'dd')<'03' then trunc(sysdate,'dd')

原值 12: else trunc(last_day(&v_date),'dd') end

新值 12: else trunc(last_day(sysdate),'dd') end

YYYYMMDD NAME

-------------- ----

01-3月 -14 WU

02-3月 -14 WANG

03-3月 -14 DONG

附 trunc函数数字用法如下：

select trunc(123.458) from dual; --123

select trunc(123.458, 0) from dual; --123

select trunc(123.458, 1) from dual; --123.4

select trunc(123.458, -1) from dual; --120

select trunc(123.458, -4) from dual; --0

select trunc(123.458, 4) from dual; --123.458

select trunc(123) from dual; --123

select trunc(123, 1) from dual; --123

select trunc(123, -1) from dual; --120

0 0