Description
Getting the first day of the week is basically straightforward once you know
the trick. Each day of the week is represented by a number from 1 to 7,
with 1 being the first day of the week. Given any day of the week, to get
back to the first day, you simply have to subtract from the current day the
number of days equal to the day of the week then add 1 day.
For example, if today is the fifth day of the week, to get back to the first
day, subtract 5 days from the current day and then add 1. So, if today is
'09/01/2005', a Thursday, which is the fifth day of the week in the U.S.
English calendar, subtracting 5 days from this date becomes '08/27/2005' then
adding 1 day becomes '08/28/2005', a Sunday, which is the first day of the week
for the date '09/01/2005'.

SET @pInputDate = CONVERT(VARCHAR(10), @pInputDate, 111)
 The first step is to get the date part of the input parameter because since
it is defined as a DATETIME data type, it may contain a time part.
Converting the input date into VARCHAR(10) with a format of 101, which is in
"YYYY/MM/DD" performs the task of getting the date part of the input
date. Since the receiving variable is defined as a DATETIME data type,
there is no need to explicitly CAST this back to DATETIME because SQL Server
will implicitly convert it to DATETIME data type.

RETURN DATEADD(DD, 1  DATEPART(DW, @pInputDate), @pInputDate)  This
step is responsible in deriving the first day of the week for the given input
date. It basically subtracts from the input date the number of days equal
to the day of the week of the input date then adds 1 day to it. It may be
confusing to see how the day of the week is subtracted from the input date then
another day is added with the formula above. Another way of writing the
above statement is DATEADD(DD, DATEPART(DW, @pInputDate) + 1, @pInputDate),
which is the equivalent of subtracting the day of the week plus 1 day.