How to add 15 working days to a given date? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to add 15 working days to a given date?

Author

Topic

raaj
Posting Yak Master

129 Posts

Posted - 2010-12-14 : 20:24:01

Hi Guys,

I am using SQL SERVER 2005.

I have a column named Order_Date

Now, I want a query that adds 15 working days (not 15 normal days) to the Order_Date column.

Any ideas?

Thanks,

Raaj

bobmcclellan
Starting Member

46 Posts

Posted - 2010-12-14 : 20:49:49

If you simply want to exclude weekends... This will do it....
date tables work great for this kind of stuff..

Create
table DateTable
( date datetime,
d int,
id int identity primary key
)

declare @cnt int, @date datetime, @d int
set @cnt = 1
set @date = '1/1/10'
while @cnt < 1000
begin
set @Date = ( select dateadd(d,1,@date) )
set @d = (select datepart(dw,@date) )
set @cnt = (select @cnt + 1 )
insert into DateTable ( [Date], d ) values (@date,@d )
end

declare @StartDate datetime
set @StartDate = '8/10/10'

Select StartDate = @StartDate,
[15 days past (no weekends)]= Max([Date])
from (
Select top 15 [Date]
from DateTable
where d not in (7,1)
and date >= @StartDate
order by ID
)x

Drop
Table DateTable

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2010-12-14 : 22:33:46

Assuming Saturday and Sunday are the only non-working days, here is another way to do it. If you do want to account for weekday holidays, you will certainly need a calendar of some sort or a table of holidays.

The query below is non-inclusive i.e., does not count the starting date in the number of days. It relies on the fact that January 1, 1900 was a Monday. (yes, I am sure. I remember that day very well).

DECLARE @startDate DATETIME;
SET @startDate = '20101115';

DECLARE @workingDays INT;
SET @workingDays = 15;
	
SELECT TOP 1
    number AS calendarDays,
    workingDays,
    DATEADD(dd,number,@startDate) AS EndDate
FROM   
    MASTER..spt_values o
    CROSS APPLY
    (
        SELECT COUNT(CASE WHEN DATEDIFF(dd, -i.number, @startDate)%7 < 5 THEN 1 END) AS workingDays
        FROM   MASTER..spt_values i
        WHERE  i.number < o.number AND i.type = 'p' AND i.number >= 0
    ) p
WHERE  
    o.number >= 0 AND o.type = 'p' 
    AND workingDays = @workingDays
ORDER BY 
    calendarDays desc

If you have a table of numbers in your database, this query will look a little cleaner. But, if such table of numbers have a million rows, the query will take a long time unless you limit the outer query.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources