How can I set last business date in SQL Server

Question

I am a new learner in SQL. I need to declare the last business date in my query. For instance,

if current day is Monday, then return last Friday.
if current day is from Tuesday to Friday, then return yesterday.

The weekends and national holiday have to be excluded.

I need this sort of format:

declare @LastBizDate as datetime
set @LastBizDate = (DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                      WHEN 'Saturday' THEN -1 
                      WHEN 'Sunday' THEN -2 
                      ELSE -1 END, DATEDIFF(DAY, 0, GETDATE())))

I really appreciate your help!

Does this answer your question? [How to get Previous business day in a week with that of current Business Day using sql server](https://stackoverflow.com/questions/9922756/how-to-get-previous-business-day-in-a-week-with-that-of-current-business-day-usi) — Thom A, Jun 28 '21 at 15:35
If you need to account for public holidays, invest in a Calendar Table; SQL Server has no idea what public holidays you observe. — Thom A, Jun 28 '21 at 15:35
If you are interested in **dates**, why are you using datetime datatype? — SMor, Jun 28 '21 at 15:47
Hi thanks I know where the problem is in my code. I should use 'Sunday' and 'Monday' instead of 'Saturday' and 'Sunday'. Larnu's link is helpful! — Cyan, Jun 28 '21 at 15:54
It also doesn't account for 'observed' holidays - where the holiday is observed on Friday when it falls on Saturday - or observed on Monday when it falls on Sunday. Nor does it account for other holidays. This is why you need - at a minimum - a holiday table that lists each holiday. — Jeff, Jun 28 '21 at 22:22

score 0 · Accepted Answer · answered Jun 28 '21 at 15:55

0

declare @LastBizDate as datetime
set @LastBizDate = CASE DATENAME(WEEKDAY, GETDATE())
                      WHEN 'Monday' THEN GETDATE()-3
                      WHEN 'Sunday' THEN GETDATE()-2
                      ELSE GETDATE()-1 END
PRINT @LastBizDate

answered Jun 28 '21 at 15:55

Meysam Kermani

41
3

1

I just noticed it. I should use 'Sunday' and 'Monday' instead of 'Saturday' and 'Sunday'. Many thanks! – Cyan Jun 28 '21 at 15:56

score 0 · Answer 2 · answered Jun 28 '21 at 16:04

Assuming that NationalHolidayIndicator or NationalHoliday are already in your date table, use a correlated subquery.

SELECT d.[Date]
, d.NationalHolidayIndicator
--, d.NationalHoliday
, (SELECT MAX([Date])
   FROM [Date] 
   WHERE [Date] < d.[Date]
     AND DATENAME(WEEKDAY, d.[Date]) NOT IN ('Saturday', 'Sunday')
     AND d.NationalHolidayIndicator = 'no'
     --AND d.NationalHoliday IS NULL
  ) as PreviousBusinessDay


FROM [Date] d

If you don't already have NationalHolidayIndicator and NationalHoliday, For the U.S. that might look something like this:

case 
  when datepart(month, dt) = 1 and datepart(day, dt) = 1 and datepart(weekday, dt) between 2 and 6 then 'yes'
  when datepart(month, dt) = 1 and datepart(day, dt) = 2 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 12 and datepart(day, dt) = 31 and datepart(weekday, dt) = 6 then 'yes'
  when datepart(month, dt) = 1 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 and datepart(year, dt) > 1985 then 'yes'
  when datepart(month, dt) = 2 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 5 and datepart(day, dt) > 24 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 7 and datepart(day, dt) = 4 and datepart(weekday, dt) between 2 and 6 then 'yes'
  when datepart(month, dt) = 7 and datepart(day, dt) = 5 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 7 and datepart(day, dt) = 3 and datepart(weekday, dt) = 6 then 'yes'
  when datepart(month, dt) = 9 and datepart(day, dt) between 1 and 7 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 10 and datepart(day, dt) between 8 and 14 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 11 and datepart(day, dt) = 11 and datepart(weekday, dt) between 2 and 6 then 'yes'
  when datepart(month, dt) = 11 and datepart(day, dt) = 12 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 11 and datepart(day, dt) = 10 and datepart(weekday, dt) = 6 then 'yes'
  when datepart(month, dt) = 11 and datepart(day, dt) between 22 and 28 and datepart(weekday, dt) = 5 then 'yes'
  when datepart(month, dt) = 12 and datepart(day, dt) = 25 and datepart(weekday, dt) between 2 and 6 then 'yes'
  when datepart(month, dt) = 12 and datepart(day, dt) = 26 and datepart(weekday, dt) = 2 then 'yes'
  when datepart(month, dt) = 12 and datepart(day, dt) = 24 and datepart(weekday, dt) = 6 then 'yes'
  else 'no'
end as NationalHolidayIndicator
, case
  when datepart(month, dt) = 1 and datepart(day, dt) = 1 then 'New Year''s Day'
  when datepart(month, dt) = 1 and datepart(day, dt) = 2 and datepart(weekday, dt) = 2 then 'New Year''s Day - legal'
  when datepart(month, dt) = 12 and datepart(day, dt) = 31 and datepart(weekday, dt) = 6 then 'New Year''s Day - legal'
  when datepart(month, dt) = 1 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 and datepart(year, dt) > 1985 then 'Martin Luther King Day'
  when datepart(month, dt) = 2 and datepart(day, dt) between 15 and 21 and datepart(weekday, dt) = 2 then 'Presidents'' Day'
  when datepart(month, dt) = 5 and datepart(day, dt) > 24 and datepart(weekday, dt) = 2 then 'Memorial Day'
  when datepart(month, dt) = 7 and datepart(day, dt) = 4 then 'Independence Day'
  when datepart(month, dt) = 7 and datepart(day, dt) = 5 and datepart(weekday, dt) = 2 then 'Independence Day - legal'
  when datepart(month, dt) = 7 and datepart(day, dt) = 3 and datepart(weekday, dt) = 6 then 'Independence Day - legal'
  when datepart(month, dt) = 9 and datepart(day, dt) between 1 and 7 and datepart(weekday, dt) = 2 then 'Labor Day'
  when datepart(month, dt) = 10 and datepart(day, dt) between 8 and 14 and datepart(weekday, dt) = 2 then 'Columbus Day'
  when datepart(month, dt) = 11 and datepart(day, dt) = 11 then 'Veterans Day'
  when datepart(month, dt) = 11 and datepart(day, dt) = 12 and datepart(weekday, dt) = 2 then 'Veterans Day - legal'
  when datepart(month, dt) = 11 and datepart(day, dt) = 10 and datepart(weekday, dt) = 6 then 'Veterans Day - legal'
  when datepart(month, dt) = 11 and datepart(day, dt) between 22 and 28 and datepart(weekday, dt) = 5 then 'Thanksgiving Day'
  when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) between 1977 and 2013 then 'Thanksgiving, Day After'
  when datepart(month, dt) = 11 and datepart(day, dateadd(day, -1, dt)) between 22 and 28 and datepart(weekday, dateadd(day, -1, dt)) = 5 and datepart(year, dt) > 2014 then 'Native American Heritage Day'
  when datepart(month, dt) = 12 and datepart(day, dt) = 25 then 'Christmas Day'
  when datepart(month, dt) = 12 and datepart(day, dt) = 26 and datepart(weekday, dt) = 2 then 'Christmas Day - legal'
  when datepart(month, dt) = 12 and datepart(day, dt) = 24 and datepart(weekday, dt) = 6 then 'Christmas Day - legal'
end as NationalHoliday

...although my holiday names and dates are from my State laws, not Federal laws.

How can I set last business date in SQL Server

2 Answers2