💡 Today I Learned

Relative Dates

This is a function that gives a whole bunch of day functions, relative to any given DateTime

The function itself

CREATE FUNCTION dbo.[DaysRelative]
(	
	-- Add the parameters for the function here
	@BaseTime DateTime
)
RETURNS TABLE 
AS
RETURN 
(
-- Declare @BaseTime DateTime = DateAdd(d, 0, GetDate());

Select
	-- Basic date terms
	@BaseTime as [Now],	
	Cast(Cast(@BaseTime as Date) as DateTime) as [Today],
	Cast(DateAdd(d, -1, Cast(@BaseTime as Date)) as DateTime) as [Yesterday],
	Cast(DateAdd(d, 1,	Cast(@BaseTime as Date)) as DateTime) as [Tomorrow],
	DateAdd(d, 7, 		Cast(Cast(@BaseTime as Date) as DateTime)) as [A_Week_Today],
	DateAdd(d, 14, 		Cast(Cast(@BaseTime as Date) as DateTime)) as [A_Fortnight_Today],
	DateAdd(d, -7, 		Cast(Cast(@BaseTime as Date) as DateTime)) as [A_Week_Ago],
	DateAdd(d, -14, 	Cast(Cast(@BaseTime as Date) as DateTime)) as [A_Fortnight_Ago],
	-- Mondays
	DateAdd(day, 1, 	DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_of_this_week_,
	DateAdd(day, 1+7, 	DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_of_next_week_,
	DateAdd(day, 1+14, 	DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_Fortnight_,
	-- This week
	DateAdd(day, 1, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_of_this_week,
	DateAdd(day, 2, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Tuesday_of_this_week,
	DateAdd(day, 3, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Wednesday_of_this_week,
	DateAdd(day, 4, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Thursday_of_this_week,
	DateAdd(day, 5, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Friday_of_this_week,
	DateAdd(day, 6, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Saturday_of_this_week,
	DateAdd(day, 7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Sunday_of_this_week,
	-- Next week
	DateAdd(day, 1+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_of_next_week,
	DateAdd(day, 2+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Tuesday_of_next_week,
	DateAdd(day, 3+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Wednesday_of_next_week,
	DateAdd(day, 4+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Thursday_of_next_week,
	DateAdd(day, 5+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Friday_of_next_week,
	DateAdd(day, 6+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Saturday_of_next_week,
	DateAdd(day, 7+7, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Sunday_of_next_week,
	-- Fortnight
	DateAdd(day, 1+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Monday_Fortnight,
	DateAdd(day, 2+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Tuesday_Fortnight,
	DateAdd(day, 3+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Wednesday_Fortnight,
	DateAdd(day, 4+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Thursday_Fortnight,
	DateAdd(day, 5+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Friday_Fortnight,
	DateAdd(day, 6+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Saturday_Fortnight,
	DateAdd(day, 7+14, DATEADD(wk, DATEDIFF(wk, 6, @BaseTime), 6)) as Sunday_Fortnight
)

Columns / Results

Given any base date (such as, the day after tomorrow), it will tell you, relative to that date when is:

See also