Get First Day and Last Day of Week – SQL Server

This time lets create the Scalar function in SQL Server to get the First Day and Last Day of the Week.

Create below Scalar function in SQL Server to get the First Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetFirstDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 1- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

Create below Scalar function in SQL Server to get the Last Day of Week.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION GetLastDayWeek
(
	-- Add the parameters for the function here
	@InputDate DateTime
)
RETURNS DateTime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Result DateTime

	-- Add the T-SQL statements to compute the return value here
	-- 1 -> Sunday, 7 -> Saturday
	SELECT @Result = DATEADD(DAY, 7- DATEPART(DW, @InputDate), @InputDate)

	-- Return the result of the function
	RETURN @Result

END
GO

To get the values from function, run below script

 select dbo.GetFirstDayWeek(GETDATE()) as StartDate
 select dbo.GetLastDayWeek(GETDATE()) as EndDate

as i said, remember that to call function we have to call like dbo.functionName