Calculate Prior Monday

It is a common business requirement to aggregate values by week and display the aggregate under the heading of either the first day of the week or the last day of the week. Calculating the first day of the week based on a calendar date is rather easy to do. We simply subtract a number of days from the input date based on the day of the week of the input date.

For example, to calculate the prior Monday of any date, the following formula works by first taking the day of the week of the input date, adding five to it, then dividing by seven and taking the remainder; we then subtract the resulting number of days from the input date. Test it out with your “dates” table, view, or table-valued function. (Don’t have a record set of dates? Get one here.)

-- Display information about a date, including the week starting date
SELECT
    N'Date' = CalendarDate
    , N'DayOfWeek' = DATEPART(WEEKDAY, CalendarDate)
    , N'NameOfWeekday' = DATENAME(WEEKDAY, CalendarDate)
    , N'PriorMonday?' = DATEADD(
        DAY
        , - ((DATEPART(WEEKDAY, CalendarDate) + 5) % 7)
        , CalendarDate)
FROM vwDates
WHERE CalendarDate BETWEEN '2012-07-01' AND '2012-07-31'
;

So, that is all well and good… but wait! Random configuration trap.

Why is it that we added five to the weekday? Five was the magic number because, by default, SQL Server is configured to start the calendar week on a Sunday. That means that Monday is considered the second day of the week. Seven days in a week minus two for Monday (which is our target day) leaves five; hence the modifier we used.

But, what if your server is configured differently and your week starts on Monday (or any day other than Sunday)? If so, you should have already noticed that the above script did not work. To correct this our script needs to be dynamic based on server configuration. Thankfully, SQL Server exposes the week day configuration via the global variable @@DATEFIRST. Furthermore, you can change the setting via a simple TSQL command. Let’s take a look:

-- Check to see your server configuration
--   Take note of this value; you might need it later
SELECT N'DATEFIRST' = @@DATEFIRST;

-- Change the first day of the week to Monday
SET DATEFIRST 1;

-- Display previous information, but add a new
--   calculation for the week starting date

SELECT
    N'Date' = CalendarDate
    , N'DayOfWeek' = DATEPART(WEEKDAY, CalendarDate)
    , N'NameOfWeekday' = DATENAME(WEEKDAY, CalendarDate)
    , N'PriorMonday?' = DATEADD(
        DAY
        , - ((DATEPART(WEEKDAY, CalendarDate) + 5) % 7)
        , CalendarDate)
    , N'PriorMonday' = DATEADD(
        DAY
        , - ((@@DATEFIRST + DATEPART(WEEKDAY, CalendarDate) - 2) % 7)
        , CalendarDate)
FROM vwDates
WHERE CalendarDate BETWEEN '2012-07-01' AND '2012-07-31'
;

This time, you should see that the “PriorMonday?” column is incorrect but the “PriorMonday” (no question mark) column is, indeed, accurate.

The skeptical ready might wonder, especially after the previous rigmarole about adding five, why should we now have to subtract two days from the weekday when the @@DATEFIRST variable is included? The answer is that when SQL Server defaults to Sunday being the first day of the week, that configuration is identified with a @@DATEFIRST value of seven (7).  As the @@DATEFIRST configuration changes, so does the DATEPART(WEEKDAY, [date]) function result. For whatever reason, the configuration value is related to the datepart/weekday result at an offset of two (2). That offset could have been anything; it just happens to be two (2). Deal with it.

Of course, it should be noted that if your organization has a custom fiscal calendar you might not need to (or even be able to)  calculate week starting/ending dates because there might not be a deterministic rule set that could be applied to a calendar date to calculate the week into which it belongs.

One last thing… don’t forget to change your @@DATEFIRST value back to its original value. No problem, really, as you were not working in your production environment. 😉

-- Change the first day of the week to Sunday (default)
--   If you had a different configuration, reset it accordingly
SET DATEFIRST 7;

Advertisements
Post a comment or leave a trackback: Trackback URL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: