Click here to Skip to main content
15,041,548 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I want to get four calendar weeks before the date of current processing date ,if four calendar weeks before date is weekend or holiday date want to get the next business day in sqlserver.
Here requirement is need exact date .I used date diff like below but i am getting 7 days for that week but we want exactly one day which is four calendar weeks before the current date.

I used like below but it is returning 7 dates like 11,12,13,14,15,16,17 complete 7 days , but my requirement is want to get exact date and if that day is week end or holiday want to get next business day .Please help on it .
SELECT DATEDIFF(week,'2021-07-17', '2021-08-14')


What I have tried:

<pre>SELECT DATEDIFF(week,'2021-07-17', '2021-08-14')
equal to 4
Posted
Updated 23-Jul-21 6:51am
Comments
CHill60 23-Jul-21 12:31pm
   
To get the exact date of 4 weeks earlier you should use
SELECT DATEADD(w, -4, @datevalue);

How are you storing your holiday dates?
sreedharmasula 23-Jul-21 12:37pm
   
Thank you chill!
if the day is weekend or holiday want to get next the business day .

1 solution

As per my comment, to get the exact date, 4 weeks earlier use
SQL
SELECT DATEADD(w, -4, @datevalue);
For the rest of the problem, you need to be able to identify holidays somehow. The way I like to do it is to have a table explicitly for dates and I like to store all dates in a year.

This may seem silly when it's easy enough to generate a list of dates, but I prefer to do this as a regular maintenance task rather than each time I need the information.

My table looks a little like this
SQL
declare CalendarDates table (ddate date, dday nvarchar(30), isWorkingday bit, isHoliday bit, comments nvarchar(50));

Then to get the next working day I just need to use
SQL
select TOP 1 *
from CalendarDates
where ddate >= @calcdate and isWorkingday = 1
ORDER BY ddate asc

Note: The TOP 1 and the ORDER BY are essential to make this work

Worked example:
SQL
insert into Calendardates(ddate, dday, isWorkingday, isHoliday, comments) values
 ('2021-Dec-14','Tuesday',1,0,'')
,('2021-Dec-15','Wednesday',1,0,'')
,('2021-Dec-16','Thursday',1,0,'')
,('2021-Dec-17','Friday',1,0,'')
,('2021-Dec-18','Saturday',0,0,'')
,('2021-Dec-19','Sunday',0,0,'')
,('2021-Dec-20','Monday',1,0,'')
,('2021-Dec-21','Tuesday',1,0,'')
,('2021-Dec-22','Wednesday',1,0,'')
,('2021-Dec-23','Thursday',1,0,'')
,('2021-Dec-24','Friday',1,0,'')
,('2021-Dec-25','Saturday',0,0,'')
,('2021-Dec-26','Sunday',0,0,'')
,('2021-Dec-27','Monday',0,1,'Christmas')
,('2021-Dec-28','Tuesday',0,1,'Boxing Day')
,('2021-Dec-29','Wednesday',1,0,'')
,('2021-Dec-30','Thursday',1,0,'')
,('2021-Dec-31','Friday',1,0,'')
,('2022-Jan-01','Saturday',0,0,'')
,('2022-Jan-02','Sunday',0,0,'')
,('2022-Jan-03','Monday',0,1,'New Year''s Day')
,('2022-Jan-04','Tuesday',1,0,'')
,('2022-Jan-05','Wednesday',1,0,'')
,('2022-Jan-06','Thursday',1,0,'');

declare @calcdate date = '2021-Dec-25';
returns
ddate		dday		isWorkingday	isHoliday	comments
2021-12-29	Wednesday	1				0	
   
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900