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. No comments yet.
  1. No trackbacks yet.