SQL Server GetStartOfWeek Scalar-Valued Function

Matt Pavey

This function is useful if you need to find the first day of the week for a given date. Because different clients and applications define the start of the week differently, it takes a parameter that lets you specify which day counts as the starting point.

I found the original on the SQL Team website.

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

    DECLARE @FirstDayOfTheWeekForMyWeek    DATETIME
    DECLARE @FirstDayOfTheWeekForFirstWeek DATETIME

    -- check for a valid day of week
    IF @WeekStarts BETWEEN 1 AND 7
    BEGIN

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

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

    END

    RETURN @FirstDayOfTheWeekForMyWeek

END

A couple of example calls:

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 result changes depending on which day you treat as the start of the week.

A colleague of mine wrote another version that does the same job. His is shorter and easier to read, and testing both against more than 750,000 records produced identical results on every row with no meaningful performance difference between them.

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

    DECLARE @Offset INT
    DECLARE @Date   DATETIME

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

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

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

    RETURN @Date

END