Transact-SQL | SQL Server | How-to Get the Week Endings for a Month
Transact-SQL doesn’t have a date function to list of the week endings for a given month. However Transact-SQL for SQL Server does have functions, like the @DateAdd or @DatePart, that can be used to calculate the week endings for a month.
The following script calculates the week ending for a given month. If the actual day of a week ending value falls in another month, that value will be excluded. Also the script maintains only one instance of each week ending so that maximum output list is always 4 items.
This script works well in the Table function, but can also be used in a stored procedure. The following example uses a Multi-statement Table-valued function. You create these from the programmability section of SQL Server under Functions.
The Transact-SQL script to extract list of Week Endings uses two input variables to calculate the week endings. These variables are the calendar month and calendar year.
List of variables
Here are the variables that will be used to determine the week endings for a particular month. For this script, it is assumed the week ending falls on a Saturday. This value can be altered using the @@DateFirst directive that can be set to a day number (1-7) to correspond to the day when the week should start (Sunday is day 1).
This value is a number between 1 - 12. It is the month that you want the week endings of. It is in a char(2) data type because we will need to build a date from date parts later.
This value is a number, stored as a char(4) for the year where you want to extract the week endings for a given month. This is a char to be able to build a date later.
This is an int and is the last day of the month, which should be either (28,29,30,31) depending on the month and if it is a leap year or not. This value is calculated using the GetLastDayOfMonth custom function. You can find complete instructions and code in this tutorial.
This variable will be used to loop through the number of days in a month until @lastDayMonth is reached.
This is the last day of the week, by default it is a Saturday. You can changed this by setting the @@DateFirst directive.
This is the date value that will be used to determine the week endings.
This is a boolean value so only one instance of each week ending is retained.
This is a table variable to temporarily hold the week ending information.
Transact-SQL Multi-statement Table-valued Function Code
CREATE FUNCTION [dbo].[WeekEndingsforMonth] ( @calMonth char(2), @calYear char(4) ) RETURNS @weekEndingTable TABLE ( LastDayOfWeek date ) AS BEGIN declare @lastDayMonth int, @counter int, @weekEnd date, @calendarDate date, @isExist int /*@isExist defaults to false so the value will be added to the @weekEndngTable table*/ set @isExist = 0 /**/ set @dayNumber = 1 --This variable defaults to the first day of the month set @CalendarDate = CONVERT(DATETIME, @calmonth +'/1/'+@calyear) --Get the last day of the month. We use the begining and ending values to set the looping parameters. set @lastDayMonth = DATEPART(dd,dbo.GetLastDayOfMonth(@CalendarDate)) /*Loop through each day of the month, calculating the week ending*/ while @dayNumber < @lastDayMonth begin --This is the actual day of the month that is updated with each iteration set @CalendarDate = CONVERT(DATETIME, @calmonth +'/'+convert(char(2), @dayNumber)+'/'+@calyear) --This statement set @weekEnd = (select DATEADD(DD,7 - datepart(DW, @CalendarDate),@CalendarDate) ) select @isExist = count(LastDayOfWeek) from @weekEndngTable where LastDayOfWeek = @weekEnd if @isExist = 0 and MONTH(@weekEnd) = @calmonth begin insert into @weekEndngTable SELECT @weekEnd end set @dayNumber = @dayNumber + 1 end /*Stop execution and return table*/ RETURN END
Detailed Information of the Code
- Start by defining the Table Function which will provide a boilerplate code that you will modify in the following steps.
- Define the @calMonth and @calYear input variables. They should be char 2 and 4 respectively. See the code below.
- The RETURN statement is provided from the template but you will to specify a return table name variable. For this exercise, I will call it @weekEndingTable. This table variable will return the result in the form of a table, so you can use like any other table including joins
- The next step is to define the internal variables that will be used to hold the various pieces of data while compiling the list of week endings. Their definitions are listing above in the Variables section.
- The operation is to set the @isExist variable to 0 to indicate that the table does not contain the week ending value. This variable is be equal to 1 if the week ending value is already in the @weekEndngTable table.
- Also you will need to set the @dayNumber to 1. This is the counter variable.
- The next variable to set is the @calendarDate. When the script is initially loaded, this variable is set to the first day of the month using the three parts of a date: @calmonth, @calyear and the day is always 1 since this is the first day of the month. The the concatenated value is converted to a datetime value using this formula: CONVERT(DATETIME, @calmonth +'/1/'+@calyear).
- We will also need to the last day of the month which we will set using the @lastDayMonth variable. This value is calculated using the Datepart Transact-SQL function to extract the day number of the date. This calculation is performed in the GetLastDayOfMonth function. The GetLastDayOfMonth Tutorial completely explains how to create this function.
- The main part of the code loops nth number of times depending on the number of days in a number. Some might argue that it would be more efficient to increment the counter variable @dayNumber by 7 and loop only 4 times. This approach would require more logic to test if only last loop the date is another month and backtrack. So I find it less error prone to loop for each day of the month. Since the code will loop at most 31 times there isn’t any performance lag. While the @dayNumber value is less the @lastDayMonth variable, the loop will continue.
- The first operation in the loop is to update the @calendarDate variable to equal the day of the month that is assigned to the @dayNumber variable.
- The @weekEnd variable is assigned the week ending date (by default Saturday) which is based on the following formula: select DATEADD(DD,7 - datepart(DW, @CalendarDate),@CalendarDate). The DATEPART T-SQL function is the inner part of the formula. The DATEPART returns the day number of week for a given date value using the DW option. The date value is provided by the @calendarDate value.
- The outer part of the equation is performed by the DATEADD T-SQL function. This function returns a new date by adding or subtracting the number of days from a given date value. So for our needs, we subtract the Day of the Week value (inner part) from 7 (the total number of days in a week) to determine which day to return based on the @calendarDate input value.
- The next statement checks to see the @weekEnd value is already in the @weekEndingTable table variable and assigns the count number to the @isExist variable. In theory, this variable should only contain a 0 or a 1
- If the @weekEnd value is not in the @weekEndingTable, in other words @isExists = 0 and the month value in the @weekEnd variable is equal to the @calMonth variable (query month) then insert the value into the table variable using the “INSERT INTO @weekEndngTable SELECT @weekEnd” statements.
- Once the insert operation is complete, we will increment the @dayNumber by 1 and the RETURN function will be called once the counter has reached the limit or the last day of the month.
Test Implementation WeekEndingsforMonth Table Function and Output
Using a Multi-statement Table-valued table is the same as using any other table and you can greatly improve query performance if the original table has too many records or as in our case we need a temporary table for our list of week endings.
You can use the @weekEndngTable as follows:
Select * from @weekEndngTable(Month, Year)
To demonstrate, replace the month and year words by real values or T-SQL valid variables such as:
Select * from @weekEndngTable(4, 2012)
Here is the output