T-SQL | How To Create Function to Get Date of Last Day of Week
Getting the last day of a given week is quick easy in T-SQL. Here is a handy bit of script that will get the last day of the week assuming that you want the default for the last day which in SQL Server it is Saturday. To change the default you have to use the SET DateFirst function.
You can use the code as is or can bundle it up into a neat Scalar-valued function and be able to use it anywhere.
--Declare a variable for the last day of the week : @endofweek
--Declare a variable for the number of days until the last day of the week: @daystillwknd
declare @endofweek datetime, @daystillwknd int
--Next get the number of days until the last day of the week using DATEPART to extract the day of the week and substracting this from the maximum number of days in a week., 7.
set @daystillwknd = (select 7 - datepart(DW, GETDATE()))
--Finally you use the DATEADD function to add the number of days until the last day of the week to the chosen date (in this example I am using the current date) which will create a new date.
set @endofweek = DATEADD(DD,@daystillwknd,GETDATE())
--Select the new date. Of course you could use the value to do other kinds of processing
As I said before, you could easily a handy function that you could use repeatedly. Here is the code to accomplish that:
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE FUNCTION dbo.GetLastDayOfWeek
( @CurrentDate datetime
BEGIN declare @endofweek datetime, @daystillwknd int
set @daystillwknd = (select 7 - datepart(DW, @CurrentDate)) set @endofweek = DATEADD(DD,@daystillwknd,@CurrentDate)
-- Return the result of the function
Now you have a neat little function that determines the last day of the week