What is the SQL syntax to create a column in my Date Dimension Table that will group the dates into this specific week grouping?

Question

I am using SQL Server 2014 and I have a Date Dimension table (called DateDimension) of which one the columns is a Date column (called Date). This table list all dates from 2000-01-01 till 2017-12-31

Extract of Date Column from DateDimension Table:

  2000-01-01 00:00:00.000
  2000-01-02 00:00:00.000
  .......................
  2017-12-31 00:00:00.000

I need a new column (let's say "Week Group") in that table that will group the above dates using the following logic:

A week starts on Monday and ends on Sunday. Thus, for dates between 03 January 2000 (which is a Monday) and 09 January 2000 (which is a Sunday), the new column will list "Wk 03-09 January 2015" and so on for the remaining dates in the Date Dimension Table.

What is the T-SQL code to achieve this?

Check out the DatePart() function : https://msdn.microsoft.com/en-us/library/ms174420.aspx?f=255&MSPPError=-2147217396. You will probably want to concatenate the year as a prefix to yield a numeric string like this "yyyyww"; then GROUP on that. — Pieter Geerkens, Jan 12 '16 at 04:52

shawnt00 · Accepted Answer · 2016-01-12T06:48:19.537

I'm assuming your @@datefirst corresponds to Sunday. And I'm also assuming that the year and month part of the text correspond to the starting day of the week rather than the ending day.

You could use this first expression in an update or possibly even as a computed column, function or view. I can't remember which of the dates functions have issues with determinism because of regional settings, so there might be some limitations.

concat(
'Wk ',
right('0' + datename(day, dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date")), 2),
'-',
right('0' + datename(day, dateadd(day, -((datepart(weekday, "Date") + 5) % 7)+6, "Date")),2),
' ',
datename(month, dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date")),
' ',
datename(year, dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date"))
)

Since there are two dates involved in the calculations you can't use the format() function as readily. Here's a two-step update that accomplishes the same thing:

update DateDimension
set WeekGroup =
    format(
        dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date"),
        'Wk dd-XX MMMM yyyy' /* leave a placeholder */
    );

update DateDimension
set WeekGroup =
    replace(
        WeekGroup,
        'XX',
        format(
            dateadd(day, -((datepart(weekday, "Date") + 5) % 7) + 6, "Date"),
            'dd'
        )
    );

If you like nesting the expressions it can of course be done in a single step:

update DateDimension
set WeekGroup =
    replace(
        format(
            dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date"),
            'Wk dd-XX MMMM yyyy' /* leave a placeholder */
        ),
        'XX',
        format(
            dateadd(day, -((datepart(weekday, "Date") + 5) % 7) + 6, "Date"),
            'dd'
        )
    );

The math is really not that complicated:

To find the start of a "week" for each date we want to determine how many days since the previous Monday. Monday is counted as day of week #2 (when @@datefirst is set to Sunday) so we want to deduct two from the "regular" weekday numbering and have it wrap around to the previous week. To avoid negative numbers in the modular arithmetic just add 5 (which is congruent to -2 mod 7). That same expression is reused to compute all the various date parts.

EDIT: In a comment you've hinted that you do need to have a slightly different format for weeks that span a month (which could include years also).

This can be handled with a case expression. Hopefully you've got the general idea and you can see where to make adjustments for the format you'd like to see applied between the two cases.

update DateDimension
set WeekGroup =
    case
        when month(dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date")) =
             month(dateadd(day, -((datepart(weekday, "Date") + 5) % 7) + 6, "Date"))
        then
            replace(
                format(
                    dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date"),
                    'Wk dd-XX MMMM yyyy' /* leave a placeholder */
                ),
                'XX',
                format(
                    dateadd(day, -((datepart(weekday, "Date") + 5) % 7) + 6, "Date"),
                    'dd'
                )
            )
        else
            concat(
                format(
                    dateadd(day, -((datepart(weekday, "Date") + 5) % 7), "Date"),
                    'Wk dd MMMM yyyy-'
                    ),
                format(
                    dateadd(day, -((datepart(weekday, "Date") + 5) % 7) + 6, "Date"),
                    'dd MMMM yyyy'
                )
            )
    end;

Thanks a lot for your effort in answering my question!! Highly appreciated. I have a created a new TEXT column in my DateDimension table called "WeekGroup" so as to execute your code. However, I am getting this error message: "Msg 8116, Level 16, State 1, Line 8 Argument data type text is invalid for argument 1 of replace function." — user3115933, Jan 12 '16 at 06:24
Yes, I've solved it by adding this syntax to your code: CONVERT(VARCHAR(MAX), WeekGroup But I am facing another minor issue with dates where the grouping involves 2 consecutive months. eg: 2011-01-26 is giving Wk 26-01 December 2011 rather than "Wk 26 Dec 11 - 01 Jan 12" — user3115933, Jan 12 '16 at 06:35
There's no reason for you to use `varchar(max)` or `text` but I'm glad you got that to work. I did comment about my assumption regarding overlapping months/years. You didn't specify in the question so I figured you only cared about the date the week started on. — shawnt00, Jan 12 '16 at 06:39

What is the SQL syntax to create a column in my Date Dimension Table that will group the dates into this specific week grouping?

1 Answers1

Linked