关于SQL server 日期处理

 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