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

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

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
Source Code

 

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

 

转载于:https://www.cnblogs.com/insus/p/10843667.html

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值