Home > T - SQL, Tips N Tricks > Last business day of any given month

Last business day of any given month

July 25th, 2009 Leave a comment Go to comments

Although every working day is a business day and are equal in value and importance, but still people eagerly wait for last business day, so as to get their salary or wage :) . Ok coming to the point, last business day is very important as there are many pending works like payrolls, inventory stocks, etc are to be processed. So to keep track of the events for this last business day is very important to keep moving your company or organization smoothly.


Here is a simple function to find out the last business day for any given date or month, if you are interested to see the information for the last business days only.

Here we are assuming our working days to be from Monday to Friday and the starting of week will be on Sunday.

Apart from this if we consider the bank holidays, national holidays, etc. then these days has to be provided in the array list or in temp table named @govtHolidays.

Call the function with any date in the given month and you will get the last business day for that month.

select dbo.getLastBusinessDay(getdate()) as LastBusinessDay

Output:
Output

The script for the function is given below:

/****** Object:  UserDefinedFunction [dbo].[getLastBusinessDay]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		
-- Create date: <25th July, 2009>
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[getLastBusinessDay]
(
	-- Add the parameters for the function here
	@anydateofMonth datetime
)
RETURNS datetime
AS
BEGIN
	-- Declare the return variable here
	DECLARE @lastBusinessDay datetime
 
	-- Add the T-SQL statements to compute the return value here
 
		DECLARE @givendate datetime
		set @givendate = @anydateofMonth
 
		DECLARE @dates TABLE
		(
		 date datetime not null
		)
 
		DECLARE @govtHolidays TABLE
		(
			Holidays datetime not null,
			Description varchar(100)
		)
 
		--Insert the govt holidays as per your need
		INSERT INTO @govtHolidays(holidays, Description)
			SELECT STR(Year(@givendate)) + '-' + 'Aug-15', 'August-15' Union All
			SELECT STR(Year(@givendate)) + '-' + 'Jan-26', 'January-26' Union All
			SELECT STR(Year(@givendate)) + '-' + 'Jan-01', 'New Year’s Day'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Jan-08', 'Muharram'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Jan-14', 'Tamil New Year''s Day / Pongal' Union All
			SELECT STR(Year(@givendate)) + '-' + 'April-10', 'Good Friday 'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Aug-13', 'Krishna Jayanthi'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Sep-21', 'Ramzan'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Oct-02', 'Gandhi Jayanthi'Union All
			SELECT STR(Year(@givendate)) + '-' + 'Dec-25', 'Christmas' 
 
		DECLARE @month int
		SET @month = MONTH(@givendate)
 
		SET @givendate = STR(Year(@givendate)) + '-' + STR(Month(@givendate)) + '-01' 
 
		WHILE datepart(MM,@givendate) = @month
		 BEGIN
			IF( @@datefirst = 7 and datepart(dw, @givendate) between 2 and 6)
			BEGIN
				INSERT INTO @dates VALUES (@givendate)
			END
			ELSE
		if(@@datefirst = 1 and datepart(dw, @givendate) between 1 and 5)
			BEGIN
				INSERT INTO @dates VALUES (@givendate);
			END
			SET @givendate = dateadd(dd, 1, @givendate);
		 END 
 
		select @lastBusinessDay = max(date) from (select date from @dates where date not in (select Holidays from @govtHolidays)) workingDays
 
	-- Return the result of the function
	RETURN @lastBusinessDay
 
END


Categories: T - SQL, Tips N Tricks
  1. venky
    April 13th, 2012 at 11:29 | #1
    Reply | Quote

    I have few queries in the above code

    Y we require this statement or y we need to process from the begining of the month
    “SET @givendate = STR(Year(@givendate)) + ‘-’ + STR(Month(@givendate)) + ‘-01′ ”
    Also in the while part the first if condition is
    IF( @@datefirst = 7 and datepart(dw, @givendate) between 2 and 6)
    why it is between 2 and 6 instead of 1 and 6.

    Also when we will go to Else since my language setting is US English, hence my @@datefirst is always 7.

    So it would be good if you breifly provided the explanation how it process the last businees day by the given code. Thank You

  1. No trackbacks yet.