SQL Server GetStartOfWeek Scalar-Valued Function

I found this function on the SQL Team website. It is helpful if you need to find the first day of the week for a specified date. Keeping in mind that the start of the week might be defined differently for various clients or applications this function has a parameter that allows you to specify which day of the week should be used as the starting point.

 

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47307

 

CREATE FUNCTION [dbo].[GetStartOfWeek]
(
     @MyDate       DATETIME,
     @WeekStarts INT = 1 -- Sun = 1, Mon = 2, Tue = 3, Wed = 4, Thu = 5, Fri = 6, Sat = 7 (Default to Sunday)
)
RETURNS DATETIME
AS
BEGIN
     -- declare variables
     DECLARE @FirstDayOfTheWeekForMyWeek DATETIME
     DECLARE @FirstDayOfTheWeekForFirstWeek DATETIME

     -- Check for valid day of week
     IF @WeekStarts BETWEEN 1 AND 7
     BEGIN
          -- find first day on or after 1753/1/1 (-53690) matching day of week of @WeekStarts
          -- 1753/1/1 is earliest possible SQL Server date.
          SELECT @FirstDayOfTheWeekForFirstWeek = CONVERT(DATETIME, -53690 + ((@WeekStarts + 5) % 7))

          -- verify beginning of week not before 1753/1/1
          IF @MyDate >= @FirstDayOfTheWeekForFirstWeek
          BEGIN
               SELECT @FirstDayOfTheWeekForMyWeek = DateAdd(dd, (DateDiff(dd, @FirstDayOfTheWeekForFirstWeek, @MyDate) / 7) * 7, @FirstDayOfTheWeekForFirstWeek)
          END
     END

     -- return value
     RETURN @FirstDayOfTheWeekForMyWeek
END

 

A couple of example calls to this function are as follows:

 

SELECT dbo.GetStartOfWeek('1/1/07', 1) -- Result: 2006-12-31
SELECT dbo.GetStartOfWeek('1/1/07', 2) -- Result: 2007-01-01

 

Notice how the results will vary based on which day you use for the start of the week.

 

Here's another version that a colleague of mine wrote that also provides the same functionality as the example above. His version is shorter and easier to read and testing against 750,000+ records yields the same results on every record and the performance difference between the two appears to be neglible.

 

CREATE FUNCTION [dbo].[fn_GetDate]
(
     @InputDate DateTime,
     @Day INT -- (1=Sunday, 2=Monday, etc)
)
RETURNS DateTime
AS
BEGIN
     DECLARE @Offset INT

     SELECT @Offset = (@Day - DATEPART(dw, @InputDate))

     IF (@Offset > 0)
          SELECT @Offset = -7 + @Offset

 

     DECLARE @Date DateTime

 

     SELECT @Date= DATEADD(dd, @Offset, CONVERT(VARCHAR, @InputDate, 101))

 

     RETURN @Date

END

 

Enjoy.