Deterministic way to get WeekEnding (Sun) for a given date column (for use in a computed column)
Take the following SQL table (fiddle here: 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 )
)
Note the use of a computed column, this computed column calculates the date of the week (Sunday) for any date in the "Booking" column, regardless of the environment variable @@datefirst
- this column MUST only contain Sundays.
This works great in production, but we need to apply an index to this computed column and for that we need to make it persistent computed column, unfortunately our use @@datefirst
makes it non-deterministic and prevents persistence and indexing.
How can we recreate this column data (ie WeekEnding for any date in the "Booking" column), but make it deterministic and ready for indexing?
PS: I would rather avoid triggers if at all possible, as the production table is already an extremely complex, high traffic table with millions of rows and multiple triggers already in place.
Edit: The final code used is:
DATEADD(DAY, 6 - DATEDIFF(DAY, CONVERT(DATE, '01/01/1990', 103), BookingDate) % 7, BookingDate)
source to share
As an alternative to the function DATEPART
, some ugly but solid solution can be used to rely on a fixed date value and then use the mod 7 date difference as an alternative to the function DATEPART
:
DATEPART(WEEKDAY, [BookingDate]) -- with @@datefirst = 1
equivalent to:
DATEDIFF(DAY, CONVERT(DATETIME,'1990-01-01',120), [BookingDate]) % 7 + 1 -- because 1990-01-01 is a monday
So use something like the following. Note: I haven't tested this yet, so you should probably play around with the offset values in the calculation [WeekEnding]
until you get it right:
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 )
)
In addition, if you have values BookingDate
that are before 1990-01-01, you may run into problems where DATEDIFF returns a negative value. In this case, adjust the fixed date value to make sure it precedes any of your values BookingDate
.
source to share