SQL问题，如何获取某月的所有周的日期范围【转语雀】

一、前言

---

记录遇到的SQL难题，如何获取某月的所有周的日期范围，且星期一为每周第一天。

二、具体实现

---

实现思路如下：

1. 知道SQL SERVER的Date数据类型和常用的Date函数
   数据类型有：DATE - 格式 YYYY-MM-DD、DATETIME - 格式: YYYY-MM-DD HH:MM:SS、SMALLDATETIME - 格式: YYYY-MM-DD HH:MM:SS、TIMESTAMP - 格式: 唯一的数字、DATETIME2、TIME...等。
   Data函数有：GETDATE()、DATEPART()、DATEADD()、DATEDIFF()、CONVERT()...等。
   参考SQL文档

2. 知道如何推算本周的开始日期与结束日期
   参考一下SQL：

--获取星期几，因为当天的星期因为得到的 weekday 比实际的多一天，所以当天减去一天，才能得到正确的星期数 
select DatePart(weekday, dateadd(dd,-1,getdate()))    

--本周一的日期
select convert(varchar(10),dateadd(dd,-(DatePart(weekday, dateadd(dd,-1,getdate()))-1),getdate()),120)

--本周日的日期
select convert(varchar(10),dateadd(dd,(7-DatePart(weekday, dateadd(dd,-1,getdate()))),getdate()),120)

--本月的开始日期
select convert(varchar(10),DATEADD(DD,-DATEPART(day,getdate())+1,getdate()),120)

--本月的最后一天日期
select convert(varchar(10),dateadd(dd,-DatePart(day,dateadd(MM,1,getdate())),dateadd(MM,1,getdate())),120)

参考博文

3. 知道如何获取按星期一开始的一年中周数
   SQL SERVER默认是每周按星期日开始的，虽然可以用 SET DATEFIRST 调整配置，但是不通用。上网找了个函数能获取按星期一开始的周数。

GO

IF OBJECT_ID('ETLWORK_GETWEEKNUMBER','FN') IS NOT NULL
DROP FUNCTION ETLWORK_GETWEEKNUMBER
GO
 
CREATE FUNCTION ETLWORK_GETWEEKNUMBER(@DATE DATETIME)  
RETURNS INTEGER
AS
BEGIN 

    DECLARE @FIRST_DATE_OF_YEAR DATETIME = DATEADD(YYYY,DATEDIFF(YYYY,0,@DATE),0) 
    -- DECLARE @MONDAY_OF_WEEK DATETIME = DATEADD(WK,DATEDIFF(WK,0,@DATE),0) 
    -- DECLARE @PREVIOUS_DATE DATETIME = DATEADD(DAY,-1,@DATE)
    DECLARE @WEEK_NUMBER INTEGER

    -- 如果当前时间是当前年的第一天
    IF @DATE = @FIRST_DATE_OF_YEAR
        SET @WEEK_NUMBER = 1 
    -- 星期天是年第一天的情况
    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 = DATEPART(WEEK,@DATE))  
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)  
    -- 星期天不是年第一天的情况
    ELSE IF (DATEPART(WEEKDAY,@DATE) = 1 AND DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,@DATE)/7 + 1 <> DATEPART(WEEK,@DATE)) 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) - 1 
    -- 如果当前天的上一个周日小于年第一天
    ELSE IF DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0)) < @FIRST_DATE_OF_YEAR 
        SET @WEEK_NUMBER = 1 
    -- 当前天前面的一个周日正好是以周日为开始年的 7 倍的天数
    ELSE IF DATEDIFF(DAYOFYEAR,@FIRST_DATE_OF_YEAR,DATEADD(DAY,-1,DATEADD(WK,DATEDIFF(WK,0,@DATE),0) ))/7 + 1 = DATEPART(WEEK,@DATE) 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE) + 1  
    ELSE 
        SET @WEEK_NUMBER = DATEPART(WEEK,@DATE)   
           
    RETURN @WEEK_NUMBER
END
GO

参考博文

4. 回归问题，如何获取某月的所有周的日期范围。其实跟用SQL画个日历差不多
   可以参考此问题，但是里面的答案试过，都有问题，只能参考思路。参考问答
   最后还是自己实现了个表值函数，依赖于上面的获取周数的标量值函数。

--列出某月的所有周的范围
CREATE FUNCTION [dbo].[fn_list_weeks_by_month]
(
	@curDate DATE
)
RETURNS 
@tmpWeekRange TABLE 
(
	weekno INT,
	month INT,
	begindate DATE,
	enddate DATE
)
AS
BEGIN
	declare @monthFirstDate date,@monthLastDate date,@tmpdate date
	--取本月第一天
	set @monthFirstDate=DATEADD(DD,-DATEPART(day,@curDate)+1,@curDate)
	--取本月最后一天 
	set @monthLastDate=dateadd(dd,-DatePart(day,dateadd(MM,1,@curDate)),dateadd(MM,1,@curDate))
	set @tmpdate=@monthFirstDate 

	--当所在周超出当月时，函数结束
	while(dateadd(dd,-(DatePart(weekday, dateadd(dd,-1,@tmpdate))-1),@tmpdate)<=@monthLastDate)
	begin
		insert into @tmpWeekRange
		values(
		dbo.ETLWORK_GETWEEKNUMBER(@tmpdate)		--调自定义函数，获取星期一开始的周数
		,MONTH(@tmpdate)
		,dateadd(dd,-(DatePart(weekday, dateadd(dd,-1,@tmpdate))-1),@tmpdate)		--取周第一天
		,dateadd(dd,(7-DatePart(weekday, dateadd(dd,-1,@tmpdate))),@tmpdate))		--取周最后一天
	
		--跳到下周
		set @tmpdate=DATEADD(DAY,7,@tmpdate)
	end

	RETURN 
END

GO

查询结果如下：