获取某一天所在周的开始日期和结束日期

指定一个日期，获取其所属周的开始日期和结束日期。以及前一周和后一周的开始日期和结束日期。

Insus.NET把这个功能写成一个自定义函数Table-values Functions。

 

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      Insus.NET
-- Create date: 2019-05-10
-- Update date: 2019-05-10
-- Description: 获取指定日期相关周的数据
-- =============================================

CREATE FUNCTION [dbo].[tvf_WeekDatas]
(
    @SpecifiedDate DATETIME
)
RETURNS @dumpWeekTable TABLE
(
    [Week] NVARCHAR(MAX) NOT NULL,
    [First Date] DATETIME NULL,
    [Last Date] DATETIME NULL
)
AS
BEGIN    
    INSERT INTO @dumpWeekTable ([Week]) VALUES ('Previous Week'),('Current Week'),('Next Week')
    
    DECLARE @dd INT = DATEDIFF(dd, 0,@SpecifiedDate)
    DECLARE @WEEKDAY INT = DATEPART(WEEKDAY, @SpecifiedDate)

    DECLARE @FD INT = 1 - @WEEKDAY
    DECLARE @LD INT = 0 - @WEEKDAY

    DECLARE @FirstDate DATETIME = DATEADD(DAY, @FD, @dd)
    DECLARE @LastDate DATETIME = DATEADD(DAY, @LD, @dd)
    
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, -1,@FirstDate), [Last Date] = DATEADD(wk, 0,@LastDate) WHERE [Week] = 'Previous Week'
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, 0,@FirstDate), [Last Date] = DATEADD(wk, 1,@LastDate) WHERE [Week] = 'Current Week'
    UPDATE @dumpWeekTable SET [First Date] = DATEADD(wk, 1,@FirstDate), [Last Date] = DATEADD(wk, 2,@LastDate) WHERE [Week] = 'Next Week'

    RETURN
END

 

如何使用上面这个函数，列举例子说明：