Share this post

Get the last Day of the week

CREATE FUNCTION [dbo].[udf_GetLastDayOfWeek]
(
	-- Add the parameters for the function here
	@Date datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @ResultVar datetime

	-- Add the T-SQL statements to compute the return value here
	
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
	/* NE PAS OUBLIER DE FAIRE UN "SET DATEFIRST 1" AVANT d'appeler cette fonction */
	/* !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! */
	
	SELECT @ResultVar =	CASE datepart(weekday,@Date)
							WHEN 1 THEN CONVERT(VARCHAR(10),(@Date+4),120)
							WHEN 2 THEN CONVERT(VARCHAR(10),(@Date+3),120)
							WHEN 3 THEN CONVERT(VARCHAR(10),(@Date+2),120)
							WHEN 4 THEN CONVERT(VARCHAR(10),(@Date+1),120)
							WHEN 5 THEN CONVERT(VARCHAR(10),(@Date),120)
							WHEN 6 THEN CONVERT(VARCHAR(10),(@Date-1),120)
							WHEN 7 THEN CONVERT(VARCHAR(10),(@Date-2),120)
						END

	-- Return the result of the function
	RETURN @ResultVar

END