sql-server - 为给定日期列获取 WeekEnding (Sun) 的确定性方法(用于计算列)

Question

采用以下 SQL 表(此处 fiddle :http://sqlfiddle.com/#!3/578bc/1/0):

CREATE TABLE [dbo].[tbl_DateExample] (
    [ID] [INT] IDENTITY(1, 1) NOT NULL ,
    [BookingDate] [DATE] NOT NULL ,
    [WeekEnding] AS ( DATEADD(DAY, ( 6 ) - ( ( DATEPART(WEEKDAY, [BookingDate]) + @@datefirst ) - ( 2 ) ) % ( 7 ), [BookingDate]) ) ,
    CONSTRAINT [PK_tbl_DateExample] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

请注意它对计算列的使用，此计算列计算 BookingDate 列中任何日期的 WeekEnding(星期日)日期，而不管 @@datefirst 环境变量 - 此列必须包含星期日仅。

这在生产中效果很好，但我们需要将索引应用于此计算列，为此我们需要使其成为持久计算列，不幸的是我们使用 @@datefirst 使得它是不确定的，并且会阻止持久性和索引编制。

我们如何重新创建此列数据(即 BookingDate 列中的任何日期的 WeekEnding(星期日))，但使其具有确定性并为索引做好准备？

PS:我宁愿尽可能避免使用触发器，因为生产表已经是一个极其复杂、高流量的表，其中包含数百万行和多个触发器。

编辑:使用的最终代码:

DATEADD(DAY, 6 - DATEDIFF(DAY, CONVERT(DATE, '01/01/1990', 103), BookingDate) % 7, BookingDate)

Answer 1

一种丑陋但可靠的解决方案是，依赖固定的日期值，然后使用日期差模 7 作为 DATEPART 函数的替代方法:

DATEPART(WEEKDAY, [BookingDate])  -- with @@datefirst = 1

相当于:

DATEDIFF(DAY, CONVERT(DATETIME,'1990-01-01',120), [BookingDate]) % 7 + 1  -- because 1990-01-01 is a monday

所以使用类似下面的东西。 注意:我还没有对此进行测试，因此您可能应该在 [WeekEnding] 计算中修改偏移值，直到正确为止:

CREATE TABLE [dbo].[tbl_DateExample] (
    [ID] [INT] IDENTITY(1, 1) NOT NULL ,
    [BookingDate] [DATE] NOT NULL ,
    [WeekEnding] AS ( DATEADD(DAY, ( 6 ) - (DATEDIFF(DAY, CONVERT(DATETIME,'1990-01-01 00:00:00.000',120), [BookingDate]) % 7), [BookingDate]) ) ,
    CONSTRAINT [PK_tbl_DateExample] PRIMARY KEY CLUSTERED ( [ID] ASC )
)

此外，如果您的 BookingDate 值早于 1990-01-01，您可能会遇到 DATEDIFF 返回负值的问题。在这种情况下，请调整固定日期值以确保它位于任何 BookingDate 值之前。