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