從周數獲取開始和結束日期SQLServer

我們已經WeekNo并Year輸入了以下格式的表格：

YYYY-ww     (2021-48)

我想要從給定的星期開始日期和結束日期，年份與上述格式相同。

如何從 SQL 查詢或程序中獲取它？

謝謝，

uj5u.com熱心網友回復：

這個怎么樣？

declare @dateText varchar(7);
declare @week int;
declare @year int;

set datefirst 1;              -- Set first day of week to Monday
set @dateText = '2021-48';
set @year = SUBSTRING(@dateText, 1, 4)
set @week = SUBSTRING(@dateText, 6, 2)

select DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4 -
     DATEPART(dw, DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4)   1

Output
-----------------
2021-11-29 00:00:00.000

或者，您可以創建一個函式：

CREATE FUNCTION dbo.GetStartOfWeek(@dateText varchar(7))
RETURNS Datetime
BEGIN
    
declare @week int;
declare @year int;
declare @offset int;

select
    @offset =  @@DATEFIRST - 1, 
    @year = SUBSTRING(@dateText, 1, 4),
    @week = SUBSTRING(@dateText, 6, 2)

 RETURN DATEADD(week, @week, DATEADD(year, @year-1900, 0)) - 4 -
        DATEPART(dw, DATEADD(week, @week, DATEADD(year, @year-1900, 0)   @offset) - 4)   1
END;

您可以在 SQL 查詢或存盤程序中使用它：

select dbo.GetStartOfWeek('2021-48') as StartDate, DATEADD(d, 7, dbo.GetStartOfWeek('2021-48')) as EndDate

uj5u.com熱心網友回復：

以下是計算查詢中年-周字串的開始和結束日期的 2 種方法。

1) 沒有 UDF

SET DATEFIRST 7; -- 7: Sunday has weekday 1
DECLARE @Test TABLE ([YEAR-WW] char(7)); 
INSERT INTO @Test VALUES 
  ('2020-53'), ('2021-01'), ('2021-02')
, ('2021-48')
, ('2021-53'), ('2022-01');

SELECT [YEAR-WW]
, [FirstDayOfWeek] = CAST(DATEADD(day, 1-DATEPART(weekday, DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-08')), DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-08')) AS DATE)
, [LastDayOfWeek]  = CAST(DATEADD(day, 6, DATEADD(day, 1-DATEPART(weekday, DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-08')), DATEADD(week, ABS(RIGHT([YEAR-WW],2))-2, LEFT([YEAR-WW],5) '01-08'))) AS DATE)
FROM @Test
ORDER BY 1;

2) 使用這個 UDF

CREATE FUNCTION dbo.GetDateFromYearWeek (
 @YearWeek VARCHAR(7) = '0000-00', -- default
 @WeekDay INT = 1, -- default
 @FirstWeekDayName VARCHAR(9) = 'monday' -- default
) RETURNS DATE
BEGIN
 
 IF @YearWeek = '0000-00'
   SET @YearWeek = CONCAT(DATEPART(year, GETDATE()), '-', DATEPART(week, GETDATE())); 
 
 IF @YearWeek NOT LIKE '[0-9][0-9][0-9][0-9]%[0-9-][0-9]'
   RETURN NULL;
 
 IF @WeekDay < 1 OR @WeekDay > 7
   RETURN NULL;
 
 DECLARE @FirstWeekDay INT = CHARINDEX(LOWER(LEFT(@FirstWeekDayName,3)), '   montuewedthufrisatsun')/3;
 IF @FirstWeekDay < 1 OR @FirstWeekDay > 7
   SET @FirstWeekDay = @@DATEFIRST;
 
 DECLARE @Year INT = TRY_CAST(LEFT(@YearWeek, 4) AS INT);
 DECLARE @Week INT = ABS(TRY_CAST(RIGHT(@YearWeek, 2) AS INT));
 
 DECLARE @Date DATE = TRY_CAST(CONCAT(@Year,'-01-08') AS DATE);
 SET @Date = DATEADD(week, @Week - 2, @Date);
 
 DECLARE @DowDiff INT = 0-((@@DATEFIRST 6-(@FirstWeekday%7))%7   DATEPART(weekday,@Date))%7;
 
 SET @Date = DATEADD(day, @DowDiff, @Date);
 SET @Date = DATEADD(day, @WeekDay-1, @Date);

 RETURN @Date;
END;

CREATE FUNCTION dbo.GetDateFromYearWeekSundayFirst (
 @YearWeek VARCHAR(7) = '0000-00',
 @WeekDay INT = 1
) RETURNS DATE
BEGIN
  RETURN dbo.GetDateFromYearWeek(@YearWeek, @Weekday, 'sunday');
END;

示例用法

SET DATEFIRST 6; -- 6:Saturday is weekday 1
DECLARE @Test TABLE ([YEAR-WW] char(7)); 
INSERT INTO @Test VALUES 
  ('2020-53'), ('2021-01'), ('2021-02')
, ('2021-48')
, ('2021-53'), ('2022-01'), ('2022-02')
;

SELECT [YEAR-WW]

, [FirstOfWeek_Mon] = dbo.GetDateFromYearWeek([YEAR-WW], 1, 'mon')
, [LastOfWeek_Mon]  = dbo.GetDateFromYearWeek([YEAR-WW], 7, 'Monday')

, [FirstOfWeek_Sun] = dbo.GetDateFromYearWeek([YEAR-WW], 1, 'SUNDAY')
, [LastOfWeek_Sun]  = dbo.GetDateFromYearWeekSundayFirst([YEAR-WW], 7)
FROM @Test
ORDER BY 1;

關于db<>fiddle 的演示在這里

標籤：sql sql-server 查询语句 周数

上一篇：如何通過獲取某些字符前后的所有內容來創建列

下一篇：如何在SQL中顯示基于多行的行差異資料？