ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Transact-SQL | SQL Server | How-to Get the Week Endings for a Month

Updated on April 19, 2012

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

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
	--This is the actual day of the month that is updated with each iteration
	set @CalendarDate = CONVERT(DATETIME, @calmonth +'/'+convert(char(2), 
	--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
		insert into @weekEndngTable
			SELECT @weekEnd 
	set @dayNumber = @dayNumber + 1
	/*Stop execution and return table*/

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

Figure 1 - Sample output using the month of April (4) and year (2012) as input values
Figure 1 - Sample output using the month of April (4) and year (2012) as input values


    0 of 8192 characters used
    Post Comment

    No comments yet.


    This website uses cookies

    As a user in the EEA, your approval is needed on a few things. To provide a better website experience, uses cookies (and other similar technologies) and may collect, process, and share personal data. Please choose which areas of our service you consent to our doing so.

    For more information on managing or withdrawing consents and how we handle data, visit our Privacy Policy at:

    Show Details
    HubPages Device IDThis is used to identify particular browsers or devices when the access the service, and is used for security reasons.
    LoginThis is necessary to sign in to the HubPages Service.
    Google RecaptchaThis is used to prevent bots and spam. (Privacy Policy)
    AkismetThis is used to detect comment spam. (Privacy Policy)
    HubPages Google AnalyticsThis is used to provide data on traffic to our website, all personally identifyable data is anonymized. (Privacy Policy)
    HubPages Traffic PixelThis is used to collect data on traffic to articles and other pages on our site. Unless you are signed in to a HubPages account, all personally identifiable information is anonymized.
    Amazon Web ServicesThis is a cloud services platform that we used to host our service. (Privacy Policy)
    CloudflareThis is a cloud CDN service that we use to efficiently deliver files required for our service to operate such as javascript, cascading style sheets, images, and videos. (Privacy Policy)
    Google Hosted LibrariesJavascript software libraries such as jQuery are loaded at endpoints on the or domains, for performance and efficiency reasons. (Privacy Policy)
    Google Custom SearchThis is feature allows you to search the site. (Privacy Policy)
    Google MapsSome articles have Google Maps embedded in them. (Privacy Policy)
    Google ChartsThis is used to display charts and graphs on articles and the author center. (Privacy Policy)
    Google AdSense Host APIThis service allows you to sign up for or associate a Google AdSense account with HubPages, so that you can earn money from ads on your articles. No data is shared unless you engage with this feature. (Privacy Policy)
    Google YouTubeSome articles have YouTube videos embedded in them. (Privacy Policy)
    VimeoSome articles have Vimeo videos embedded in them. (Privacy Policy)
    PaypalThis is used for a registered author who enrolls in the HubPages Earnings program and requests to be paid via PayPal. No data is shared with Paypal unless you engage with this feature. (Privacy Policy)
    Facebook LoginYou can use this to streamline signing up for, or signing in to your Hubpages account. No data is shared with Facebook unless you engage with this feature. (Privacy Policy)
    MavenThis supports the Maven widget and search functionality. (Privacy Policy)
    Google AdSenseThis is an ad network. (Privacy Policy)
    Google DoubleClickGoogle provides ad serving technology and runs an ad network. (Privacy Policy)
    Index ExchangeThis is an ad network. (Privacy Policy)
    SovrnThis is an ad network. (Privacy Policy)
    Facebook AdsThis is an ad network. (Privacy Policy)
    Amazon Unified Ad MarketplaceThis is an ad network. (Privacy Policy)
    AppNexusThis is an ad network. (Privacy Policy)
    OpenxThis is an ad network. (Privacy Policy)
    Rubicon ProjectThis is an ad network. (Privacy Policy)
    TripleLiftThis is an ad network. (Privacy Policy)
    Say MediaWe partner with Say Media to deliver ad campaigns on our sites. (Privacy Policy)
    Remarketing PixelsWe may use remarketing pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to advertise the HubPages Service to people that have visited our sites.
    Conversion Tracking PixelsWe may use conversion tracking pixels from advertising networks such as Google AdWords, Bing Ads, and Facebook in order to identify when an advertisement has successfully resulted in the desired action, such as signing up for the HubPages Service or publishing an article on the HubPages Service.
    Author Google AnalyticsThis is used to provide traffic data and reports to the authors of articles on the HubPages Service. (Privacy Policy)
    ComscoreComScore is a media measurement and analytics company providing marketing data and analytics to enterprises, media and advertising agencies, and publishers. Non-consent will result in ComScore only processing obfuscated personal data. (Privacy Policy)
    Amazon Tracking PixelSome articles display amazon products as part of the Amazon Affiliate program, this pixel provides traffic statistics for those products (Privacy Policy)