sql取月初月底、季年周等日期范围sql(许多网上博客有BUG)

因网上有的sqlserver写法存在BUG，所以特写此文

比如取月底日期的sql,许多博文是这样取的，其实是错误的

select dateadd(day,-day(getdate()),dateadd(month,1,getdate()))

错误的！！！

正确的写法我示范两种

select 
dateadd(day,-1,dateadd(month,1,dateadd(dd,-datepart(dd,getdate())+1,getdate()))) ,--月初加1月减一天
dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())),--加1月减去下月的天数  
dateadd(day,-day(getdate()),dateadd(month,1,getdate())) --错误写法

更多的日期范围界限取法见下

select getdate() as 当日,
dateadd(dd,-1,getdate()) as 昨日, 
dateadd(dd,-2,getdate()) as 前日,
dateadd(dd,-datepart(dd,getdate())+1,getdate()) as 月初,
dateadd(dd,-day(getdate())+1,getdate()) as 月初_写法2,
dateadd(day,-1,dateadd(month,1,dateadd(dd,-datepart(dd,getdate())+1,getdate()))) as 月底,--月初加1月减一天
dateadd(dd,-day(dateadd(month,1,getdate())),dateadd(month,1,getdate())) as 月底_写法2,--加1月减去下月的天数  
--dateadd(day,-day(getdate()),dateadd(month,1,getdate())) as 月底,2018-01-31取到的是2018-01-28 22:02:13.183
dateadd(dd,-day(dateadd(month,1,getdate()))+1,dateadd(month,1,getdate())) as 下月初,
dateadd(dd,-day(dateadd(month,2,getdate())),dateadd(month,2,getdate())) as 下月底, 
dateadd(quarter,datediff(quarter,0,getdate())-1,0) as 上季初,
dateadd(quarter,datediff(quarter,0,getdate()),-1) as 上季末,
dateadd(quarter,datediff(quarter,0,getdate()),0) as 季初,
dateadd(quarter,1+datediff(quarter,0,getdate()),-1) as 季末,
dateadd(quarter,1+datediff(quarter,0,getdate()),0) as 下季初,
dateadd(quarter,2+datediff(quarter,0,getdate()),-1) as 下季末,
dateadd(month,datediff(month,0,getdate())-1,0) as 上月初,
dateadd(dd,-day(dateadd(month,-1,getdate()))+1,dateadd(month,-1,getdate())) as 上月初_写法2,
dateadd(day,-1,dateadd(day,1-datepart(day,getdate()),getdate())) as 上月底,
dateadd(dd,-day(getdate()),getdate()) as 上月底_写法2,  
dateadd(month,datediff(month,0,getdate())-2,0) as 前月初,
dateadd(day,-1,dateadd(month,datediff(month,0,getdate())-1,0)) as 前月底,
dateadd(year,datediff(year,0,getdate()),0) as 年初,
dateadd(year,datediff(year,-1,getdate()),-1) as 年底,
dateadd(year,datediff(year,0,getdate())-1,0) as 去年年初,
dateadd(year,datediff(year,0,getdate()),-1) as 去年年末,
dateadd(year,1+datediff(year,0,getdate()),0) as 明年年初,
dateadd(year,1+datediff(year,-1,getdate()),-1) as 明年年末,
dateadd(weekday,1-datepart(weekday,getdate()),getdate()) as 周初,
dateadd(day,7,dateadd(weekday,-datepart(weekday,getdate()),getdate())) as 周底,
--dateadd(week,datediff(week,-1,getdate()),-1) as 周底,
dateadd(week,-1,dateadd(day,1-datepart(weekday,getdate()),getdate())) as 上周初,
dateadd(weekday,-datepart(weekday,getdate()),getdate()) as 上周底,
dateadd(week,-2,dateadd(day,1-datepart(weekday,getdate()),getdate())) as 前周初,
dateadd(week,-1,dateadd(day,-datepart(weekday,getdate()),getdate())) as 前周底

注意，默认周初不是星期一，是周日，需要调整的话，先执行

set DATEFIRST 1

这样符合中国习惯