ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

T-SQL | How To Create Function to Get Date of Last Day of Week

Updated on May 16, 2012


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

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article