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
select @endofweek
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

GO

SET QUOTED_IDENTIFIER ON

GO
CREATE FUNCTION dbo.GetLastDayOfWeek

( @CurrentDate datetime

)

RETURNS datetime

AS

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

RETURN @endofweek
END

GO


Now you have a neat little function that determines the last day of the week

More by this Author


Comments

No comments yet.

    Sign in or sign up and post using a HubPages Network account.

    0 of 8192 characters used
    Post Comment

    No HTML is allowed in comments, but URLs will be hyperlinked. Comments are not for promoting your articles or other sites.


    Click to Rate This Article
    working