Sql server : how to get four calendar week before date of current date in SQL server

Question

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 23-Jul-21 6:05am

sreedharmasula

Updated 23-Jul-21 6:51am

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

CHill60 · Answer 1 · 2021-07-23T06:51:00

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