1.关于SQLSERVER 处理日期在工作中遇到的方式和函数
1.Datediff(month, g.GetMoneyDate, Getdate()) = 0 -- 获取当月数据
2.Datediff(month, g.GetMoneyDate, Getdate()) = 1 -- 获取上一个月数据
3.Datediff(year, g.GetMoneyDate, Getdate()) = 0 --获取当年数据
4.Datediff(year, g.GetMoneyDate, Getdate()) = 1 --获取上一年度数据
5.Datediff(Quarter, g.GetMoneyDate, Getdate()) = 0 --获取当个季度数据
6.Datediff(Quarter, g.GetMoneyDate, Getdate()) = 0 --获取上个季度数据
7.Datediff(year, g.GetMoneyDate, Getdate()) = 1 and Datediff(month, g.GetMoneyDate, Getdate()) >=12
--获取上年度到某个月份的数据,比如,今日日期是2022-07-13 ,那么获取的就是2021-1-1到2021-7-31的数据
8.select * from ProjectManager
where RegisterTime > dateadd(month, -6, getdate()) //注册日期是否是6个月之前
2.获取当前月度的每一个日期:
select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) day
from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%'
结果:
3.获取当前月第一天和最后一天
当月最后一天:select dateadd (dd,-day(getdate ()) , dateadd (m,1 , getdate()))
当月第一天: select dateadd (dd,-day (getdate())+1,getdate(0)
4.获取当前周的所有日期
select date=DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --本周一
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 1) --本周二
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 2) --本周三
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 3) --本周四
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 4) --本周五
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) --本周六
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 6) --本周日
5.判断日期是周几
case
when datepart(weekday,EntrustDate)=1 then '周天'
when datepart(weekday,EntrustDate)=2 then '周一'
when datepart(weekday,EntrustDate)=3 then '周二'
when datepart(weekday,EntrustDate)=4 then '周三'
when datepart(weekday,EntrustDate)=5 then '周四'
when datepart(weekday,EntrustDate)=6 then '周五'
when datepart(weekday,EntrustDate)=7 then '周六'
end as '周几'
select DATENAME(weekday,EntrustDate) as '周几' from EntrustBills
6.关于根据当前周每天,当前月每天,当前季每月,当前年每月 日期统计
--周模式开始
with A as (
select date=DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --本周一
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 1) --本周二
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 2) --本周三
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 3) --本周四
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 4) --本周五
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 5) --本周六
union all select DATEADD(wk, DATEDIFF(wk,0,getdate()), 6) --本周日
)
select convert(varchar(10),a1.date,120) as rq , DATENAME(weekday,a1.date) as xq,isnull(b.num,0) as tj from A a1 LEFT JOIN
(select EntrustDate,COUNT(*) as num from EntrustBills group by EntrustDate) b
on b.EntrustDate=a1.date;
--周模式end
--月模式开始
with A as (select convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) date
from
(select substring(convert(varchar,GETDATE(),120),1,7)+'-01' day) t1,
(select number from MASTER..spt_values WHERE TYPE='P' AND number>=0 and number<=31) t2
where convert(varchar(10),dateadd(DAY,t2.number,t1.day),120) like substring(convert(varchar,GETDATE(),120),1,7)+'%')
select convert(varchar(10),a1.date,120) as rq , DATENAME(weekday,a1.date) as xq,isnull(b.num,0) as tj from A a1 LEFT JOIN
(select EntrustDate,COUNT(*) as num from EntrustBills group by EntrustDate) b
on b.EntrustDate=a1.date;
--月模式end
--季模式开始
with A as(
select date=convert(char(7), DATEADD(QUARTER,DATEDIFF(QUARTER,0,getdate()), 0), 120) --本季度第一月
union all select convert(char(7), DATEADD(mm,1,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)), 120) --本季度第二月
union all select convert(char(7), DATEADD(mm,2,DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)), 120) --本季度第三月
)
select a1.date as rq ,'' as xq,isnull(b.num,0) as tj from A a1 LEFT JOIN
(select convert(varchar(7),EntrustDate,120) as mm,COUNT(*) as num from EntrustBills group by convert(varchar(7),EntrustDate,120)) b
on b.mm =a1.date;
--季模式end
--年模式开始
with A as (
select date= convert(char(7),DATEADD(YY,DATEDIFF(YY,0,getdate()), 0),120) --当年第一月
union all select date= convert(char(7), DATEADD(mm,1,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120) --当年第二月
union all select date= convert(char(7), DATEADD(mm,2,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,3,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,4,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,5,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,6,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,7,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,8,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,9,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,10,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
union all select date= convert(char(7), DATEADD(mm,11,DATEADD(YY,DATEDIFF(YY,0,getdate()), 0)),120)
)
select a1.date as rq ,'' as xq,isnull(b.num,0) as tj from A a1 LEFT JOIN
(select convert(varchar(7),EntrustDate,120) as mm,COUNT(*) as num from EntrustBills group by convert(varchar(7),EntrustDate,120)) b
on b.mm =a1.date;
--年模式end