Last Business Day of Each Month
-
Hello All,
How is your day going? I hope that all is well.
Does anyone have SQL code to determine the last business day of each month. I do not want to hard code each date. I have the code to pull the last day of each month, but not the last business day. Can anyone provide this for me.
Thanks
Andrew SQLDBA
-
Depends on your business. Is Saturday a business day ? What if a public holiday falls on a business day right before a weekend at the end of the month ?
-
Just in general, I can take care of the ones that fall odd. Saturday is not a normal business day. Monday - friday is normal business days of teh week.
Thanks
Andrew SQLDBA
-
Since you already know the last day of the month, I guess you could write a proc passing in that date and in a loop test it for being a weekday and if not subtract 1 day and loop. When you hit a weekday then return that date.
-
You can try this UDF - it should do what you need:
Create Function dbo.ufn_LastBusinessDayOfMonth(
@dt datetime
)
RETURNS datetime
AS
BEGIN
Declare @dt2 datetime
Declare @Df int
Declare @dSat int
Declare @dSun int
Select @dt2 = DATEADD(d, -1, DATEADD(m, 1 + DATEDIFF(m, 0, @dt), 0))
-- Since we cannot use SET DATEFIRST within a stored procedure, we must improvise
-- Default is 7 (Sunday). We want the equivalent of 6 (Saturday)
Select @dSat = datepart(dw, '2000-01-01') -- Known Saturday
Select @dSun = (@dSat % 7) + 1
Select @dt2 = (
CASE WHEN Datepart(dw, @dt2) = @dSun Then DateAdd(day, -2, @dt2)
WHEN Datepart(dw, @dt2) = @dSat Then DateAdd(day, -1, @dt2)
ELSE
END)
Return @dt2
END
-
This could be converted to a UDF to replace GETDATE() with a date for the month to find the last business day of...
The time included in the date takes you to 23:59:59.997 which is the closest you can get without SQL server rounding up to the next day. It's handy to have the time in this calc so you can compare with <=.
--===== Find last business day of current month (not including holidays)
SELECT DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))
- CASE DATENAME(dw,DATEADD(ms,-3,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)))
WHEN 'SUNDAY' THEN 2
WHEN 'SATURDAY' THEN 1
ELSE 0
END AS LastBusinessCurrentMonth
However, depending on what you want to do, you may want to strip the time element off (remove the outer DATEADD) and add 1 to you can compare with < for guaranteed accuracy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hi there....recently had our MI guys ask me for such functions too.
As we needed to know first of month, last of month, last working day etc, have a number of functions. 2 below will do what you need, and a little more
FirstLast takes 3 args: any valid date, offset in days (0=current,1=next,-1 last etc), and type 'F' for first of, 'L' for last of. So to get last day 3 months previous getdate(),-3,'L' etc
CREATE Function dbo.fn_FirstLast (
@TheDate datetime,
@Offset int,
@type char(1)
 
RETURNS smalldatetime
AS
begin
declare @workingdate smalldatetime
select @workingdate =
cast('01/' +
CAST(MONTH(DATEADD(MONTH,-@offset,@thedate))as char(2)) + '/'+
CAST(YEAR(DATEADD(MONTH,-@offset,@thedate))as char(4)) as smalldatetime)
if UPPER(@type) ='L'
SET @workingdate= dateadd(mm,1,@workingdate)-1
RETURN @workingdate
END
Second function then just takes date and type, so for your purposes, you just need to call this one (it uses the other one)
call as dbo.fn_FirstLastWorkingDay with the date and 'F' and 'L' for first and last
Create function fn_FirstLastWorkingDay (@thedate smalldatetime, @type char(1))
returns smalldatetime
as
begin
declare @localdate smalldatetime
set @localdate=dbo.fn_FirstLast(@thedate,0,@type)
if datename(dw,@localdate) in ('Saturday','Sunday')
set @localdate=dbo.fn_FirstLastWorkingDay(@localdate,@type)
return @localdate
end
Long answer to your question, but might give you a little more flexibility
ross
-
Here's a bunch of ways to find different start- endings regarding weeks, months and such..
-- The example shows how to find out on which dates a week
-- starts and ends, given an arbitrary date.
-- Start of weeks are on Mondays, and ends on Fridays
set datefirst 1 -- Make sure week starts on a monday
declare @today datetime
set @today = '2004-03-23'
select @today as 'present time',
dateadd(day, 1 - datepart(weekday, @today), @today) as 'first_weekday',
dateadd(day, 5 - datepart(weekday, @today), @today) as 'last_weekday' -- friday
-- 2003-03-11 / Kenneth Wilhelmsson
-- This is a sample matrix showing how to find out dates of month boundries
-- from any given point in time.
set nocount on
declare @date datetime
set @date = getdate() -- the point in time from which to measure
print '''Today''s date'' is: ' + convert(char(10), @date, 121)
print ' '
-- date of the 1st of the current month
select convert(char(6), @date, 112) + '01' as '1st this month'
-- date of the last day of current month
select dateadd(day, -1, dateadd(month, 1, convert(char(6), @date, 112) + '01')) as 'last this month'
-- date of the 1st of the previous month
select dateadd(month, -1, convert(char(6), @date, 112) + '01') as '1st of last month'
-- date of the last day of the previous month
select dateadd(day, -1, convert(char(6), @date, 112) + '01') as 'last of last month'
-- date of the 1st of the next month
select dateadd(month, 1, convert(char(6), @date, 112) + '01') as '1st of next month'
-- date of the last day of the next month
select dateadd(day, -1, dateadd(month, 2, convert(char(6), @date, 112) + '01')) as 'last of next month'
set nocount off
-- alternative way of finding first day in the month of a given in-date
declare @date datetime
set @date = '1898-06-04 12:42:28.653' -- the point in time from which to measure
select DATEADD(mm, DATEDIFF(mm,0,@date), 0)
/Kenneth
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply