ename='JONES' then hiredate end) as jones_hd from emp where ename in ('BLAKE','JONES') )
x,t500 where t500.id<=blake_hd-jones_hd+1
postgresql
select sum(case when trim(to_char(jones_hd+t500.id-1,'DAY')) in ('SATURDAY','SUNDAY') then 0
else 1 end) as days from (select max(case when ename='BLAKE' then hiredate end) as
blake_hd,max(case when ename='JONES' then hiredate end) as jones_hd from emp where ename in
('BLAKE','JONES') ) x,t500 where t500.id<=blake_hd-jones_hd+1
sqlserver
select sum(case when datename(dw,jones_hd+t500.id-1) in ('SATURDAY','SUNDAY') then 0 else 1
end) as days from ( select max(case when ename='BLAKE' then hiredate end) as blake_hd,max
(case when ename='JONES' then hiredate end) as jones_hd from emp where ename in
('BLAKE','JONES') ) x,t500 where t500.id<=datediff(day,jones_hd-blake_hd)+1
六十二、计算2个日期之间的月和年
db2/mysql
select mnth,mnth/12 from (select (year(max_hd)-year(min_hd))*12+(month(max_hd)-month
(min_hd)) as mnth from (select min(hiredate) as min_hd,max(hiredate) as max_hd from emp ) x ) y
oracle
select months_between(max_hd,min_hd),months_between(max_hd,min_hd)/12 from (select min
(hiredate) min_hd,max(hiredate) max_hd from emp ) x
postgresql
select mnth,mnth/12 from ( select extract(year from max_hd) extract(year from min_hd))*12 +
(extract(month from max_hd) extract(month from min_hd)) as mnth from (select min(hiredate)
as min_hd,max(hiredate) as max_hd from emp ) x ) y
sqlserver
select datediff(month,min_hd,max_hd),datediff(month,min_hd,max_hd)/12 from (select min
(hiredate) min_hd,max(hiredate) max_hd from emp ) x
六十三、计算2个日期之间的秒、分、小时 db2
select dy*24 hr,dy*24*60 min,day*24*60*60 sec from (select (days(max(case when ename='WARD'
then hiredate end))-days(max(case when ename='ALLEN' then hiredate end)) ) as dy from emp ) x
mysql/sqlserver
select datediff(day,allen_hd,ward_hd)*24 hr,datediff(day,allen_hd,ward_hd)*24*60
min,datediff(day,allen_hd,ward_hd)*24*60*60 sec from (select max(case when ename='WARD' then
hiredate end) as ward_hd,max(case when ename='ALLEN' then hiredate end) as allen_hd from emp ) x
oracle/postgresql
select dy*24 as hr,dy*24*60 as min,dy*24*60*60 as sec from (select (max(case when
ename='WARD' then hiredate end)-max(case when ename='ALLEN' then hiredate end)) as dy from
emp ) x
六十四、统计一年中的周日的天数 db2
with x (start_date,end_date) as ( select start_date,start_date+1 year end_date from (select
(current_date dayofyear(current_date) day)+1 day as start_date from t1) tmp union all select
start_date+1 day,end_date from x where start_date+1 day (start_date),count(*) from x group by dayname(start_date) mysql select date_format(date_add(cast(concat(year(current_date),'-01-01') as date),interval t500.id-1 day),'%W') day,count(*) from t500 where t500.id<=datediff(cast(concat(year (current_date)+1,'-01-01') as date),cast(concat(year(current_date),'-01-01') as date)) group by date(format(date_add(cast(concat(year(current_date),'-01-01') as date),interval t500.id-1 day),'%W') oracle with x as (select level lvl from dual connect by level<=(add_months(trunc(sysdate,'y'),12)- trunc(sysdate,'y'))) select to_char(trunc(sysdate,'y')+lvl-1,'DAY'),count(*) from x group by to_char(trunc(sysdate,'y')+lvl-1,'DAY') select to_char(trunc(sysdate,'y')+rownum-1,'DAY'),count(*) from t500 where rownum<= (add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y')) group by to_char(trunc(sysdate,'y') +rownum-1,'DAY') postgresql select to_char(cast(date_trunc('year',current_date) as date+gs.id-1,'DAY'),count(*) from generate_series(1,366) gs(id) where gs.id<=(cast(date_trunc('year',current_date)+interval '12 month' as date)-cast(date_trunc('year',current_date) as date)) group by to_char(cast (date_trunc('year',current_date) as date) + gs.id-1,'DAY') sqlserver with x (start_date,end_date) as (select start_date,dateadd(year,1,start_date) end_date from (select cast(cast(year(getdate()) as varchar) +'-01-01' as datetime) start_date from t1 ) tmp union all select dateadd(day,1,start_date),end_date from x where dateadd (day,1,start_date) datename(dw,start_date) OPTION (MAXRECURSION 366) 六十五、查看2个记录之间的日期不同 db2 select x.*,days(x.next_hd)-days(x.hiredate) diff from (select e.deptno,e.name,e.hiredate, (select min(d.hiredate) from emp d where d.hiredate>e.hiredate) next_hd from emp e where e.deptno=10) x mysql/sqlserver select x.*,datediff(day,x.hiredate,x.next_hd) diff from (select e.deptno,e.ename,e.hiredate,(select min(d.hiredate) from emp d where d.hiredate>e.hiredate) next_hd from emp e where e.deptno=10 ) x oracle select ename,hiredate,next_hd,next_hd-hiredate diff from (select deptno,ename,hiredate,lead (hiredate) over(order by hiredate) next_hd from emp ) where deptno=10 postgresql select x.*,x.next_hd-x.hiredate as diff from (select e.deptno,e.ename,e.hiredate,(select min(d.hiredate) from emp d where d.hiredate>e.hiredate) as next_hd from emp e where e.deptno=10 ) x 六十六、判断这一年是不是闰年 db2 with x (dy,mth) as ( select dy,month(dy) from (select (current_date-dayofyear(current_date) days+1 days)+1 months as dy from t1) tmp1 union all select dy+1 days,mth from x where month (dy+1 day)=mth) select max(day(dy) from x oracle select to_char(last_day(add_months(trunc(sysdate,'y'),1)),'DD') from t1 postgresql select max(to_char(tmp2.dy+x.id,'DD')) as dy from ( select dy,to_char(dy,'MM') as mth from (select cast(cast(date_trunc('year',current_date) as date)+interval '1 month' as date) as dy from t1) tmp1 ) tmp2,generate_series (0,29) x(id) where to_char(tmp2.dy+x.id,'MM')=tmp2.mth mysql select day(last_day(date_add(date_add(date_add(current_date,interval -dayofyear (current_date) day),interval 1 day),interval 1 month))) dy from t1 sqlserver with x (dy,mth) as (select dy,month(dy) from (select dateadd(mm,1,(getdate()-datepart (dy,getdate()))+1) dy from t1) tmp1 union all select dateadd(dd,1,dy),mth from x where month(dateadd(dd,1,dy))=mth) select max(day(dy)) from x 六十七、计算这一年中有多少天 db2 select days((curr_year+1 year))-days(curr_year) from (select (current_date-dayofyear (current_date) day+1 day) curr_year from t1 ) x oracle select add_months(trunc(sysdate,'y'),12)-trunc(sysdate,'y') from dual postgresql select cast((curr_year+interval '1 year') as date) - curr_year from ( select cast (date_trunc('year',current_date) as date) as curr_year from t1 ) x mysql select datediff((curr_year+interval 1 year),curr_year) from ( select adddate(current_date,- dayofyear(current_date)+1) curr_year from t1 ) x sqlserver select datediff(d,curr_year,dateadd(yy,1,curr_year)) from ( select dateadd(d,-datepart (dy,getdate())+1,getdate()) curr_year from t1 ) x 六十八、分解日期----小时、分、秒、日、月、年 db2 select hour(current_timestamp) hr,minute(current_timestamp) min,second(current_timestamp) sec,day(current_timestamp) dy,month(current_timestamp) mth,year(current_timestamp) yr from