五十九、增加或减少日，月，年
db2
select hiredate -5 day as hd_minus_5D,hiredate +5 day as hd_plus_5D,hiredate -5 month as
hd_minus_5M,hiredate +5 month as hd_plus_5M,hiredate -5 year as hd_minus_5Y,hiredate +5 year
as hd_plus_5Y from emp where deptno=10
oracle
select hiredate-5 as hd_minus_5D,hiredate+5 as hd_plus_5D,add_months(hiredate,-5) as
hd_minus_5M,add_months(hiredate,5) as hd_plus_5M,add_months(hiredate,-5*12) as
hd_minus_5Y,add_months(hiredate,5*120 as hd_plus_5Y from emp where deptno=10
postgresql
select hiredate-interval '5 day' as hd_minus_5D,hiredate+interval '5 day' as
hd_plus_5D,hiredate-interval '5 month' as hd_minus_5M,hiredate+interval '5 month' as
hd_plus_5M,hiredate-interval '5 year' as hd_minus_5Y,hiredate+interval '5 year' as
hd_plus_5Y from emp where deptno=10
mysql
select hiredate-interval 5 day as hd_minus_5D,hiredate+interval 5 day as
hd_plus_5D,hiredate-interval 5 month as hd_minus_5M,hiredate+interval 5 month as
hd_plus_5M,hiredate-interval 5 year as hd_minus_5Y,hiredate+interval 5 year as hd_plus_5Y
from emp where deptno=10
select date_add(hiredate,interval -5 day) as hd_minus_5D,date_add(hiredate,interval 5 day)
as hd_plus_5D,date_add(hiredate,interval -5 month) as hd_minus_5M,date_add(hiredate,interval
5 month) as hd_plus_5M,date_add(hiredate,interval -5 year) as hd_minus_5Y,date_add
(hiredate,interval 5 year) as hd_plus_5Y from emp where deptno=10
sqlserver
select dateadd(day,-5,hiredate) as hd_minus_5D,dateadd(day,5,hiredate0 as
hd_plus_5D,dateadd(month,-5,hiredate) as hd_minus_5M,dateadd(month,5,hiredate) as
hd_plus_5M,dateadd(year,-5,hiredate) as hd_minus_5Y,dateadd(year,5,hiredate) as hd_plus_5Y
from emp where deptno=10
六十、计算2个日期之间的天数
db2
select days(ward_hd) - days(allen_hd) from (select hiredate as ward_hd from emp where
ename='WARD') x,(select hiredate as allen_hd from emp where ename='ALLEN') y
oracle/postgresql
select ward_hd - allen_hd from (select hiredate as ward_hd from emp where ename='WARD') x,
(select hiredate as allen_hd from emp where ename='ALLEN') y
mysql/sqlserver
select datediff(day,allen_hd,ward_hd) from select hiredate as ward_hd from emp where ename =
'WARD') x,(select hiredate as allen_hd from emp where ename='ALLEN') y
六十一、计算2个日期之间的工作日天数
db2
select sum(case when dayname(jones_hd+t500.id day -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
mysql
select sum(case when date_format(date_add(jones_hd,interval t500.id-1 DAY),'%a') in
('Sat','Sun') 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(blake_hd,jones_hd)+1
oracle
select sum(case when to_char(jones_hd+t500.id-1,'DY') in ('SAT','SUN') 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
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<end_date) select dayname
(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)<end_date) select datename(dw,start-date),count(*) from x group by
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
t1
oracle
select to_number(to_char(sysdate,'hh24')) hour,to_number(to_char(sysdate,'mi'))
min,to_number(to_char(sysdate,'ss')) sec,to_number(to_char(sysdate,'dd')) day,to_number
(to_char(sysdate,'mm')) mth,to_number(to_char(sysdate,'yyyy')) year from dual
postgresql
select to_number(to_char(current_timestamp,'hh24'),'99') as hr,to_number(to_char
(current_timestamp,'mi'),'99') as min,to_number(to_char(current_timestamp,'ss'),'99') as
sec,to_number(to_char(current_timestamp,'dd'),'99') as day,to_number(to_char
(current_timestamp,'mm'),'99') as mth,to_number(to_char(current_timestamp,'yyyy'),'9999') as
yr from t1
mysql
select date_format(current_timestamp,'%k') hr,date_format(current_timestamp,'%i')
min,date_format(current_timestamp,'%s') sec,date_format(current_timestamp,'%d')
dy,date_format(current_timestamp,'%m') mon,date_format(current_timestamp,'%Y') yr from t1
sqlserver
select datepart(hour,getdate()) hr,datepart(minute,getdate()) min,datepart(second,getdate())
sec,datepart(day,getdate()) dy,datepart(month,getdate()) mon,datepart(year,getdate()) yr
from t1
六十九、计算一个月的第一天和最后一天
db2
select (current_date-day(current_date) day+1 day) firstday,(current_date+1 month -day
(current_date) day) lastday from t1
oracle
select trunc(sysdate,'mm') firstday,last_day(sysdate) lastday from dual
postgresql
select firstday,cast(firstday+interval '1 month'-interval '1 day' as date) as lastday from (
select cast(date_trunc('month',current_date) as date) as firstday from t1) x
mysql
select date_add(current_date,interval -day(current_date)+1 day) firstday,last_day
(current_date) lastday from t1
sqlserver
select dateadd(day,-day(getdate())+1,getdate()) firstday,dateadd(day,-day(getdate(),dateadd
(month,1,getdate())) lastdate from t1
七十、计算出一年中的一周中某一天的日期
db2
with x (dy,yr) as ( select dy,year(dy) yr from (select (current_date-dayofyear(current_date)
days +1 days) as dy from t1) tmp1 union all select dy+1 days,yr from x where year(dy+1 day)
=yr) select dy from x where dayname(dy)='Friday'
oracle
with x as (select trunc(sysdate,'y')+level-1 dy from dual connect by level<=add_months
(trunc(sysdate,'y'),12)-trunc(sysdate,'y')) select * from x where to_char(dy-1,'d') ='5'
postgresql
select cast(date_trunc('year',current_date) as date)+x.id as dy from generate_series( 0,
(select cast(cast(date_trunc('year',current_date) as date) + interval '1 years' as date)-
cast(date_trunc('year',current_date) as date))-1 ) x(id) where to_char(cast(date_trunc
('year',current_date) as date)+x.id,'dy')='fri'
mysql
select dy from (select adddate(x.dy,interval t500.id-1 day) dy from (select dy,year(dy) yr
from (select adddate(adddate(current_date,interval -dayofyear(current_date) day),interval 1
day ) dy from t1) tmp1 ) x,t500 where year(adddate(x.dy,interval t500.id-1 day))=x.yr) tmp2
where dayname(dy)='Friday'
sqlserver
with x (dy,yr) as ( select dy,year(dy) yr from ( select getdate()-datepart(dy,getdate())+1
dy from t1) tmp1 union all select dateadd(dd,1,dy),yr from x where year(dateadd(dd,1,dy))
=yr) select x.dy from x where datename(dw,x.dy)='Friday' option (maxrecursion 400)
七十一、确定这个月中的第一次出现和最后一次出现的特定的周末
db2
with x (dy,mth,is_monday) as ( select dy,month(dy),case when dayname(dy)='Monday' then 1
else 0 end from (select (current_date-day(current_date) day +1 day) dy from t1) tmp1 union
all select (dy +1 day),mth,case when dayname(dy +1 day)='Monday' then 1 else 0 end from x
where month(dy +1 day)=mth) select min(dy) first_month,max(dy) last_monday from x where
is_monday=1
oracle
select next_day(trunc(sysdate,'mm')-1,'星期一') first_monday,next_day(last_day(trunc
(sysdate,'mm'))-7,'星期一') last_monday from dual
postgresql
select first_monday,case to_char(first_monday+28,'mm') when mth then first_monday+28 else
first_monday+21 end as last_monday from (select case sign(cast(to_char(dy,'d') as integer)
-2) when 0 then dy when -1 then dy+abs(cast(to_char(dy,'d') as integer)-2) when 1 then (7-
(cast(to_char(dy,'d') as integer)-2))+dy end as first_monday,mth from (select cast
(date_trunc('month',current_date) as date) as dy,to_char(current_date,'mm') as mth from t1 )
x ) y
mysql
select first_monday,case month(adddate(first_monday,28)) when mth then adddate
(first_monday,28) else adddate(first_monday,21) end last_monday from ( select case sign
(dayofweek(dy)-2) when 0 then dy when -1 then adddate(dy,abs(dayofweek(dy)-2)) when 1 then
adddate(dy,(7-(dayofweek(dy)-2))) end first_monday,mth from (select adddate(adddate
(current_date,-day(current_date)),1) dy,month(current_date0 mth from t1) x ) y
sqlserver
with x (dy,mth,is_monday) as ( select dy,mth,case when datepart(dw,dy)=2 then 1 else 0 end
from (select dateadd(day,1,dateadd(day,-day(getdate()),getdate())) dy,month(getdate()) mth
from t1 ) tmp1 union all select dateadd(day,1,dy),mth,case when datepart(dw,dateadd
(day,1,dy))=2 then 1 else 0 end from x where month(dateadd(day,1,dy))=mth ) select min(dy)
first_monday,max(dy) last_monday from x where is_monday=1
七十二、创建一个日历
db2
with x (dy,dm,mth,dw,wk) as ( select (current_date -day(current_date) day +1 day) dy,day
((current_date -day(current_date) day +1 day)) dm,month(current_date) mth,dayofweek
(current_date -day(current_date) day +1 day) dw,week(iso(current_date -day(current_date) day
+1 day) wk from t1 union all select dy+1 day,day(dy+1 day),mth,dayofweek(dy+1 day),week_iso
(dy+1 day) from x where month(dy+1 day)=mth ) select max(case dw when 2 then dm end) as
Mo,max(case dw when 3 then dm end) as Tu,max(case dw when 4 then dm end) as We,max(case dw
when 5 then dm end) as Th,max(case dw when 6 then dm end) as Fr,max(case dw when 7 then dm
end) as Sa,max(case dw when 1 then dm end) as Su from x group by wk order by wk
oracle
with x as ( select * from (select to_char(trunc(sysdate,'mm')+level-1,'iw') wk,to_char
(trunc(sysdate,'mm')+level-1,'dd') dm,to_number(to_char(trunc(sysdate,'mm')+level-1,'d'))
dw,to_char(trunc(sysdate,'mm')+level-1,'mm') curr_mth,to_char(sysdate,'mm') mth from dual
connect by level<=31) where curr_mth<=31) select max(case dw when 2 then dm end) as Mo,max
(case dw when 3 then dm end) as Tu,max(case dw when 4 then dm end) as We,max(case dw when 5
then dm end) as Th,max(case dw when 6 then dm end) as Fr,max(case dw when 7 then dm end) as
Sa,max(case dw when 1 then dm end) as Su from x group by wk order by wk
postgresql
select max(case dw when 2 then dm end) as Mo,max(case dw when 3 then dm end) as Tu,max(case
dw when 4 then dm end) as We,max(case dw when 5 then dm end) as Th,max(case dw when 6 then
dm end) as Fr,max(case dw when 7 then dm end) as Sa,max(case dw when 1 then dm end) as Su
from (select * from ( select cast(date_trunc('month',current_date) as date) +x.id,to_char
(cast(date_trunc('month',current_date0 as date)+x.id,'iw') as wk,to_char(cast(date_trunc
('month',current_date) as date) +x.id,'dd') as dm,cast(to-char(cast(date_trunc
('month',current_date) as date) +x.id,'d') as integer) as dw,to_char(cast(date_trunc
('month',current_date) as date) +x.id,'mm') as curr_mth,to_char(current_date,'mm') as mth
from generate_series (0,31) x(id) ) x where mth=curr_mth ) y group by wk order by wk
mysql
select max(case dw when 2 then dm end) as Mo,max(case dw when 3 then dm end) as Tu,max(case
dw when 4 then dm end) as We,max(case dw when 5 then dm end) as Th,max(case dw when 6 then
dm end) as Fr,max(case dw when 7 then dm end) as Sa,max(case dw when 1 then dm end) as Su
from (select date_format(dy,'%u') wk,date_format(dy,'%d') dm,date_format(dy,'%w')+1 dw from
(select adddate(x.dy,t500.id-1) dy,x.mth from (select adddate(current_date,-dayofmonth
(current_date)+1) dy,date_format(adddate(current_date,-dayomonth(current_date)+1),'%m') mth
from t1) x,t500 where t500.id<=31 and date_format(adddate(x.dy,t500.id-1),'%m')=x.mth) y ) z
group by wk order by wk
sqlserver
with x (dy,dm,mth,dw,wk) as ( select dy,day(dy) dm,datepart(m,dy) mth,datepart(dw,dy)
dw,case when datepart(dw,dy)+1 then datepart(ww,dy)-1 else datepart(ww,dy) end wk from
(select dateadd(day,-day(getdate())+1,getdate()) dy from t1 ) x union all select dateadd
(d,1,dy),day(dateadd(d,1,dy)),mth,datepart(dw,dateadd(d,1,dy)),case when datepart
(dw,dateadd(d,1,dy))=1 then datepart(wk,dateadd(d,1,dy))-1 else datepart(wk,dateadd(d,1,dy))
end from x where datepart(m,dateadd(d,1,dy))=mth) select max(case dw when 2 then dm end) as
Mo,max(case dw when 3 then dm end) as Tu,max(case dw when 4 then dm end) as We,max(case dw
when 5 then dm end) as Th,max(case dw when 6 then dm end) as Fr,max(case dw when 7 then dm
end) as Sa,max(case dw when 1 then dm end) as Su from x group by wk order by wk
七十三、显示一年中的每季度的第一天和最后一天
db2
select quarter(dy-1 day) QTR,day-3 month Q_start,dy-1 day Q_end from (select (current_date-
(dayofyear(current_date)-1) day +(rn*3) month) dy from (select row_number() over() rn from
emp fetch first 4 rows only ) x ) y
oracle
select rownum qtr,add_months(trunc(sysdate,'y'),(rownum-1)*3) q_start,add_months(trunc
(sysdate,'y'),rownum*3)-1 q_end from emp where rownum<=4
postgresql
select to_char(dy,'Q') as QTR,date(date_trunc('month',dy)-(2*interval '1 month')) as
Q_start,dy as Q_end from (select date(dy+((rn*3)*interval '1 month'))-1 as dy from (select
rn,date(date_trunc('year',current_date)) as dy from generate_series(1,4) gs(rn) ) x ) y
mysql
select quarter(adddate(dy,-1)) QTR,date_add(dy,interval -3 month) Q_start,adddate(dy,-1)
Q_end from (select date_add(dy,interval (3*id) month) dy from (select id,adddate
(current_date,-dayofyear(current_date)+1) dy from t500 where id<=4) x ) y
sqlserver
with x (dy,cnt) as (select dateadd(d,-(datepart(dy,getdate())-1),getdate()),1 from t1 union
all select dateadd(m,3,dy),cnt+1 from x where cnt+1<=4) select datepart(q,dateadd(d,-1,dy))
QTR,dateadd(m,-3,dy) Q_start,dateadd(d,-1,dy) Q_end from x order by 1
七十四、显示给定季度中的最开始和最后的日期
db2
select (q_end-2 month) q_start,(q_end+1 month)-1 day q-end from (select date(substr(cast(yrq
as char(4)),1,4)||'-'||rtrim(cast(mod(yrq,10)*3 as char(2))) ||'-1') q_end from (select
20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq from t1
union all select 20054 yrq from t1) x ) y
oracle
select add_months(q_end,-2) q_start,last_day(q_end) q_end from ( select to_date(substr
(yrq,1,4)||mod(yrq,10)*3,'yyyymm') q_end from (select 20051 yrq from dual union all select
20052 yrq from dual union all select 20053 yrq from dual union all select 20054 yrq from
dual) x ) y
postgresql
select date(q_end-(2*interval '1 month')) as q_start,date(q_end+interval '1 month'-interval
'1 day') as q_end from (select to_date(substr(yrq,1,4)||mod(yrq,10)*3,'yyyymm') as q_end
from (select 20051 yrq from t1 union all select 20052 yrq from t1 union all select 20053 yrq
from t1 union all select 20054 yrq from t1) x ) y
mysql
select date_add(adddate(q_end,-day(q_end)+1),interval -2 month) q_start,q_end from (select
last_day(str_to_date(concat(substr(yrq,1,4),mod(yrq,10)*3),'%Y%m')) q_end from (select 20051
as yrq from t1 union all select 20052 as yrq from t1 union all select 20053 as yrq from t1
union all select 20054 as yrq from t1) x ) y
sqlserver
select dateadd(m,-2,q_end) q_start,dateadd(d,-1,dateadd(m,1,q_end)) q_end from (select cast
(substring(cast(yrq as varchar),1,4)+'-'+cast(yrq%10*3 as varchar)+'-1' as datetime) q_end
from (select 20051 as yrq from t1 union all select 20052 as yrq from t1 union all select
20053 as yrq from t1 union all select 20054 as yrq from t1) x ) y
七十五、填充丢失的日期
db2
with x (start_date,end_date) as ( select (min(hiredate) dayofyear(min(hiredate)) day +1 day)
start_date,(max(hiredate) dayofyear(max(hiredate)) day +1 day) +1 year end_date from emp
union all select start_date +1 month,end_date from x where (start_date +1 month)<end_date)
select x.start_date mth,count(e.hiredate) num_hired from x left join emp e on
(x.start_date=(e.hiredate-day(hiredate)-1 day)) group by x.start_date order by 1
oracle
with x as (select add_months(start_date,level-1) start_date from (select min(trunc
(hiredate,'y')) start_date,add_months(max(trunc(hiredate,'y')),12) end_date from emp )
connect by level<=months_between(end_date,start_date)) select x.start_date MTH,count
(e.hiredate) num_hired from x,emp e where x.start_date=trunc(e.hiredate(+),'mm') group by
x.start_date order by 1
with x as (select add_months(start_date,level-1) start-date from (select min(trunc
(hiredate,'y')) start_date,add_months(max(trunc(hiredate,'y')),12) end_date from emp )
connect by level <=months_between(end_date,start_date)) select x.start_date MTH,count
(e.hiredate) num_hired from x left join emp e on (x.start_date=trunc(e.hiredate,'mm')) group
by x.start_date order by 1
postgresql
create view v as select cast(extract year from age(last_month,first_month))*12-1 as integer
) as mths from (select cast(date_trunc('year',min(hiredate)) as date ) as first_month,cast
(cast(date_trunc('year',max(hiredate)) as date) + interval '1 year' as date) as last_month
from emp ) x
select y.mth,count(e.hiredate) as num_hired from (select cast(e.start_date+(x.id*interval '1
month') as date) as mth from generate_series (0,(select mths from v)) x(id),(select cast
(date_trunc('year',min(hiredate)) as date) as start_date from emp ) e) y left join emp e on
(y.mth=date_trunc('month',e.hiredate)) group by y.mth order by 1
mysql
select z.mth,count(e.hiredate) num_hired from (select date_add(min_hd,interval t500.id-1
month) mth from (select min_hd,date_add(max_hd,interval 11 month) max_hd from (select
adddate(min(hiredate),-dayofyear(min(hiredate))+1) min_hd,adddate(max(hiredate),-dayofyear
(max(hiredate))+10 max_hd from emp ) x ) y,t500 where date-add(min_hd,interval t500.id-1
month)<max_hd ) z left join emp e on (z.mth=adddate(date_add(last_day(e.hiredate),interval
-1 month),1)) group by z.mth order by 1
sqlserver
with x (start_date,end_date) as (select (min(hiredate) datepart(dy,min(hiredate))+1)
start_date,dateadd(yy,1,max(hiredate) datepart(dy,max(hiredate))+1)) end_date from emp union
all select dateadd(mm,1,start_date),end_date from x where dateadd(mm,1,start_date)<end_date)
select x.start_date mth,count(e.hiredate) num_hired from x left join emp e on
(x.start_date=dateadd(dd,-day(e.hiredate)+1,e.hiredate)) group by x.start_date order by 1
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
