Calculate the last working day of the previous month

Question

Post reply

Calculate the last working day of the previous month

HookSQLDBA7

SSChampion

Points: 10709
More actions
August 30, 2005 at 7:15 am

#90646

What is a quick and easy way to calculate the last working day of the previous month using a SQL Statement. (We are using SQL Server 2000, SP 3).
Thanks in advance, Kevin
The following SQL Statement is what I developed to calculate the last working day of the previous month. I did use some of the suggestions I received. The key was to get the previous business date from an existing data table (MellonTrans_Temp), use it to determine the previous month and year, and then use the previous month and year to pull the last working date (of that month and year) from an existing Dates Table. Please let me know what you think and if my solution would work in all situations (i.e., If the month is January, leap year, etc.)
DECLARE @PrevBusinessDate1 AS varchar(10)
DECLARE @PrevBusinessDate1_Month AS varchar(2)
DECLARE @PrevBusinessDate1_Year AS smallint
/* Get Max Price Date (previous business date) From Mellon Table.*/
DECLARE @PriceDate AS varchar(10)
SET @PriceDate = (
SELECT
CONVERT(char(12), Max(PriceDate), 101)
FROM MellonTrans_Temp
WHERE SUBSTRING(PortName, 1, 8) = 'VRSF4595'
)
/*Set @PrevBusinessDate1 to Max Price Date on Mellon Table.*/
SET @PrevBusinessDate1 = @PriceDate
SET @PrevBusinessDate1_Month = MONTH (DATEADD (mm, -1, @PrevBusinessDate1))
/*Determine @PrevBusinessDate1_Year using @PrevBusinessDate1_Month and the Year in @PrevBusinessDate1. If @PrevBusinessDate1_Month is 1 then subtract 1 from the Year.*/
SELECT @PrevBusinessDate1_Year = (
SELECT NewDate =
  CASE
  /*Compare @PrevBusinessDate1_Month to 12 because 1 has already been subtracted. If @PrevBusinessDate1_Month 12 then subtract 1 from Year. */
  WHEN @PrevBusinessDate1_Month = 12 THEN YEAR (DATEADD (yy, -1, @PrevBusinessDate1))
  ELSE YEAR (@PrevBusinessDate1)
  END
)
/* Select the PrevMonthLastCalendarDate (Max(Date)) from the Dates Table where MONTH(Date) = @PrevBusinessDate1_Month AND YEAR(Date) = @PrevBusinessDate1_Year, and Date is not a Weekend or Holiday Date. */
DECLARE @PrevMonthLastCalendarDate AS DateTime
SET @PrevMonthLastCalendarDate = (
SELECT MAX(Date) AS PrevMonthLastCalendarDate FROM FISQL.DBO.Dates
WHERE MONTH(Date) = @PrevBusinessDate1_Month
AND YEAR(Date) = @PrevBusinessDate1_Year
AND DATEPART(dw, Date) NOT IN (6, 7) /* Eliminate Weekend Dates */
AND Date NOT IN (SELECT Holiday_Date FROM Holidays)
)
SELECT @PrevMonthLastCalendarDate

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Do you have a calendar table and a holidays table??

The query would look something like this :

Select max(C.date) as date from dbo.Calendar C left outer join dbo.holidays H on C.date = H.date where C.date between @DateStart and @DateEnd and datepart(dw, C.date) < 6 and H.Date is null

AndyLoughman SSC-Addicted Points: 422 More actions · Answer 2

You could try something like this...

select CASE

When datepart(dw, dateadd(d, -datepart(d, getdate()), getdate())) = 7

Then dateadd(d, -datepart(d, getdate()), getdate()) - 1

When datepart(dw, dateadd(d, -datepart(d, getdate()), getdate())) = 1

Then dateadd(d, -datepart(d, getdate()), getdate()) - 2

Else

dateadd(d, -datepart(d, getdate()), getdate())

End as LastWorkingday

This does not take any holidays into account. If you want to do so you will have to build a table of holidays. The above only returns the last Weekday of the previous month.

Hope it helps!

Nikhil Khimani SSC Enthusiast Points: 174 More actions · Answer 3

Try this ...

Select DateAdd(mm, DateDiff(mm, 0, dateadd(mm, -1, Getdate())),0) -- First date of previous month

Select DateAdd(dd, -1, DateAdd(mm, DateDiff(mm, 0, Getdate()),0)) -- Last date of previous month

hr_sn Ten Centuries Points: 1092 More actions · Answer 4

If you are just looking for a last date of previous month you can also try this:

select getdate() - datepart(d,getdate())

philcart SSC-Forever Points: 47794 More actions · Answer 5

How about this to show the last weekday in the month ?? The important bit is the first line.

SET DATEFIRST 1
DECLARE @MyDate datetime

-- example 1 with end of month on a weekend
SET @MyDate = '1 Aug 2005'
SET @MyDate = @MyDate - DATEPART(d,@MyDate)

IF DATEPART(dw, @MyDate) > 5
    SET @MyDate = DATEADD(dd, -(DATEPART(dw, @MyDate)-5), @MyDate)
    
SELECT @MyDate, DATEPART(dw, @MyDate), DATENAME(dw, @MyDate)

-- example 2 with end of month during the week
SET @MyDate = GetDate()
SET @MyDate = @MyDate - DATEPART(d,@MyDate)

IF DATEPART(dw, @MyDate) > 5
    SET @MyDate = DATEADD(dd, -(DATEPART(dw, @MyDate)-5), @MyDate)
    
SELECT @MyDate, DATEPART(dw, @MyDate), DATENAME(dw, @MyDate)

--------------------
Colt 45 - the original point and click interface