Frequently used statements in SQL Server query dates, such as the first day of the week, the week of the year, useful

--本周第一天    SELECT DATEADD( Day ,1-(DATEPART(Weekday,getdate())[email protected]@DATEFIRST-1)%7,getdate())   --or    select dateadd(wk, datediff(wk,0,getdate()), 0)   --本周第一天    select dateadd(wk, datediff(wk,0,getdate()), 6)      --上月第一天    SELECT CONVERT ( CHAR (10),DATEADD( month ,-1,DATEADD(dd,- DAY (GETDATE())+1,GETDATE())),111)   --上月最后一天    SELECT CONVERT ( CHAR (10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+ ‘ 23:59:59‘ --本月第一天    select dateadd(dd,-datepart(dd,getdate())+1,getdate())   --本月最后一天    select dateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))   --本月天数    select datediff(dd,dateadd(dd,-datepart(dd,getdate())+1,getdate()), dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate())))   --or    select datepart(dd,dateadd(dd,-1,dateadd(mm,1, cast ( cast ( year (getdate())  as varchar )+ ‘-‘ + cast ( month (getdate())  as varchar )+ ‘-01‘ as datetime))))      --下月第一天    select dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))   --下月最后一天    SELECT CONVERT ( CHAR (10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+2,0)),111)+ ‘ 23:59:59‘ --季度第一天    SELECT DATEADD(qq,   DATEDIFF(qq,0,getdate()),   0)    --季度最后一天（直接推算法）    SELECT DATEADD( Day ,-1, CONVERT ( char (8),DATEADD( Month ,1+DATEPART(Quarter,getdate())*3- Month (getdate()),getdate()),120)+ ‘1‘ )   --季度的最后一天（CASE判断法）    select DATEADD( Month ,DATEPART(Quarter,getdate())*3- Month (getdate()),getdate())      --本月第一个星期一    SELECT DATEADD(wk, DATEDIFF(wk,  ‘‘ , DATEADD(dd, 6 -  DAY (getdate()), getdate())),  ‘‘ )    --去年最后一天    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))    --今年第一天    SELECT DATEADD(yy,   DATEDIFF(yy,0,getdate()),   0)    --今年最后一天    SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1,0))      --指定日期所在周的任意一天    SELECT DATEADD( Day ,@number-DATEPART(Weekday,@dt),@dt) --5．指定日期所在周的任意星期几    --A.  星期天做为一周的第1天    SELECT DATEADD( Day ,@number-(DATEPART(Weekday,@dt)[email protected]@DATEFIRST-1)%7,@dt)   --B.  星期一做为一周的第1天    SELECT DATEADD( Day ,@number-(DATEPART(Weekday,@dt)[email protected]@DATEFIRST-2)%7-1,@dt)   ---周内的第几日    select datepart(weekday,getdate())  as 周内的第几日   --年内的第几周    select datepart(week,getdate())  as 年内的第几周   --年内的第几季    select datepart(quarter,getdate())  as 年内的第几季         --判断某天是当月的第几周的sql函数     CREATE FUNCTION WeekOfMonth(@ day datetime)      RETURNS int AS begin    ----declare @day datetime    declare @num  int declare @Start datetime   declare @dd  int declare @dayofweek  char (8)   declare @dayofweek_num  char (8)   declare @startWeekDays  int ---set @day=‘2009-07-05‘    if datepart(dd,@ day )=1   return 1   else set @Start= ( SELECT DATEADD(mm,   DATEDIFF(mm,0,@ day ),   0))  --一个月第一天的    set @dayofweek= (datename(weekday,@Start))  ---得到本月第一天是周几    set @dayofweek_num=( select ( case @dayofweek  when ‘星期一‘ then 2   when ‘星期二‘ then 3   when ‘星期三‘ then 4   when ‘星期四‘ then 5   when ‘星期五‘ then 6   when ‘星期六‘ then 7   when ‘星期日‘ then 1   end ))   set @dayofweek_num= [email protected]_num+1  ---得到本月的第一周一共有几天    ---print @dayofweek_num        set @dd=datepart(dd,@ day )  ----得到今天是这个月的第几天    --print @dd    if @dd<[email protected]_num  --小于前一周的天数    return 1   else set @[email protected]@dayofweek_num   if @dd % 7=0         begin         set @[email protected] / 7            return @num+1                   end     else --if @dd % 7<>0                set @[email protected] / 7   set @[email protected]+1+1           return @num   end

Frequently used statements in SQL Server query dates, such as the first day of the week, the week of the year, useful