select 开点,到点,trunc(decode(sign(到点-to_date('2005-06-01','yyyy-mm-dd')),1,to_date('2005-06-01','yyyy-mm-dd'),到点)-decode(sign(开点-to_date('2005-01-01','yyyy-mm-dd')),1,开点,to_date('2005-01-01','yyyy-mm-dd'))) 到点开点 from tbname
select table.*,decode(to_char(date_from,'MM')||to_char(date_to,'MM'), '0405',trunc(date_to)-trunc(last_day(date_from)), '0505',trunc(date_to)-trunc(date_from), '0506',trunc(last_day(date_from))-trunc(date_from)) num from table where to_char(date_from,'YYYYMM') = '200505' or to_char(date_to,'YYYYMM') = '200505'不是很严密,如果存在2005-03-30 12:23:33 2005-05-05 12:23:33 这样的记录的话还需要修改 2005-04-30 12:23:33 2005-05-05 12:23:33 //这条应该是5 天 这条算得是2005-05-05 到2005-04-30之间de天数,如果算到2005-05-01就只有4天
SQL> desc test Name Type Nullable Default Comments ---------- ------------ -------- ------- -------- START_TIME VARCHAR2(14) Y END_TIME VARCHAR2(14) Y SQL> select * from test;START_TIME END_TIME -------------- -------------- 20050430122333 20050505122333 20050501122333 20050507122333 20050530122333 20050606122333SQL> SQL> select start_time,end_time, 2 (case when substr(end_time,5,2)='06' and substr(start_time,5,2)='05' 3 then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss')) 4 when substr(end_time,5,2)='05' and substr(start_time,5,2)='05' 5 then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss')) 6 when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05' 7 then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd'))) 8 end ) as tt from test;START_TIME END_TIME TT -------------- -------------- ---------- 20050430122333 20050505122333 5 20050501122333 20050507122333 6 20050530122333 20050606122333 1
补充: select start_time,end_time, (case when substr(end_time,5,2)>'05' and substr(start_time,5,2)='05' then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss')) when substr(end_time,5,2)='05' and substr(start_time,5,2)='05' then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss')) when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05' then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd'))) end ) as tt from test; 如果是date类型可以用to_char(start_time,'d') 得到月份
select table.*,decode(to_char(date_from,'MM')||to_char(date_to,'MM'),
'0405',trunc(date_to)-trunc(last_day(date_from)),
'0505',trunc(date_to)-trunc(date_from),
'0506',trunc(last_day(date_from))-trunc(date_from)) num
from table
where to_char(date_from,'YYYYMM') = '200505' or to_char(date_to,'YYYYMM') = '200505'不是很严密,如果存在2005-03-30 12:23:33 2005-05-05 12:23:33 这样的记录的话还需要修改
2005-04-30 12:23:33 2005-05-05 12:23:33 //这条应该是5 天
这条算得是2005-05-05 到2005-04-30之间de天数,如果算到2005-05-01就只有4天
Name Type Nullable Default Comments
---------- ------------ -------- ------- --------
START_TIME VARCHAR2(14) Y
END_TIME VARCHAR2(14) Y SQL> select * from test;START_TIME END_TIME
-------------- --------------
20050430122333 20050505122333
20050501122333 20050507122333
20050530122333 20050606122333SQL>
SQL> select start_time,end_time,
2 (case when substr(end_time,5,2)='06' and substr(start_time,5,2)='05'
3 then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss'))
4 when substr(end_time,5,2)='05' and substr(start_time,5,2)='05'
5 then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss'))
6 when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05'
7 then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd')))
8 end ) as tt from test;START_TIME END_TIME TT
-------------- -------------- ----------
20050430122333 20050505122333 5
20050501122333 20050507122333 6
20050530122333 20050606122333 1
select start_time,end_time,
(case when substr(end_time,5,2)>'05' and substr(start_time,5,2)='05'
then trunc(last_day(to_date(start_time,'yyyymmddhh24miss')))-trunc(to_date(start_time,'yyyymmddhh24miss'))
when substr(end_time,5,2)='05' and substr(start_time,5,2)='05'
then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(to_date(start_time,'yyyymmddhh24miss'))
when substr(end_time,5,2)='05' and substr(start_time,5,2)<'05'
then trunc(to_date(end_time,'yyyymmddhh24miss'))-trunc(last_day(to_date('20050401','yyyymmdd')))
end ) as tt from test;
如果是date类型可以用to_char(start_time,'d') 得到月份