SQL 常用的年、月、日、周、季度日期语句

 

DECLARE @TimeTable Table(Name nvarchar(50), TimePoint datetime )  

Insert into @TimeTable(Name, TimePoint) 

select '去年第一天', DATEADD(year, DATEDIFF(year,0,getdate())-1, 0) 

Insert into @TimeTable(Name, TimePoint) 

select '今年第一天', DATEADD(year, DATEDIFF(year,0,getdate()), 0) 

Insert into @TimeTable(Name, TimePoint) 

select '上月第一天', DATEADD(mm,DATEDIFF(mm,0,dateadd(month,-1,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

select '本月第一天',DATEADD(mm,DATEDIFF(mm,0,dateadd(month,0,getdate())),0) 

Insert into @TimeTable(Name, TimePoint) 

select '昨天零点',DATEADD(dd,DATEDIFF(dd,0,dateadd(day,-1,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

select '今天零点',DATEADD(dd,DATEDIFF(dd,0,dateadd(day,0,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

select '上一小时',DATEADD(HH,DATEDIFF(HH,0,dateadd(HH,-1,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

select '当前整时',DATEADD(HH,DATEDIFF(HH,0,dateadd(HH,0,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

Select '上季度的第一天', DATEADD(qq,DATEDIFF(qq,0,dateadd(qq,-1,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

Select '季度的第一天',DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)

Insert into @TimeTable(Name, TimePoint) 

Select '上周第一天', DATEADD(wk,DATEDIFF(wk,0,dateadd(wk,-1,getdate())),0)

Insert into @TimeTable(Name, TimePoint) 

Select '本周第一天', DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)

select * from @TimeTable