Common Dates For Reporting Services And T-SQL (Part 2)

Sometimes I need to get weeks ending on a certain day, like the week ending as if on Tuesday, or Saturday. It's pretty easy in SSRS expressions, but it's not so easy in T-SQL (if you want to account for @@Datefirst which jumbles the week starting date up). But I did finally work it out and have prepared samples below!

Sometimes I need to get weeks ending on a certain day, like the week ending as if on Tuesday, or Saturday. It's pretty easy in SSRS expressions, but it's not so easy in T-SQL (if you want to account for SET DATEFIRST which jumbles the week starting date up). But I did finally work it out and have prepared samples below!

=DateTime.Today.AddDays(IIf(DateTime.Today.DayOfWeek <= X, 0, 7) + X - DateTime.Today.DayOfWeek)
'
' Replace the two 'X' marks with the DayOfWeek number below, and Today with whichever date you're working on:
'   0 = Sunday
'   1 = Monday
'   2 = Tuesday
'   3 = Wednesday
'   4 = Thursday
'   5 = Friday
'   6 = Sunday

Alter  Function dbo.WeekEnding (@Date Date, @DayOfWeek Int)
Returns Date
As
Begin
		Return	Dateadd(Day, (
				Case
				When	Datepart(dw, @Date) + @@Datefirst - 
						Case 
						When	Datepart(Weekday, @Date) >= ( 8 - @@Datefirst ) 
						Then	8
						Else	1 
						End <= @DayOfWeek
				Then	0
				Else	7
				End + @DayOfWeek
				) - ( Datepart(Weekday, @Date) + @@Datefirst - 
						Case 
						When	Datepart(Weekday, @Date) >= ( 8 - @@Datefirst ) 
						Then	8
						Else	1 
						End ), @Date)
End
Go

-- Get the next Friday
Select	Getdate() As Today,
		dbo.WeekEnding(Getdate(), 5) As Friday
Go