Calculate workdays from a date

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

General SQL Server Forums

Script Library

Calculate workdays from a date

Author

Topic

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 08:37:15

This is one way you can add a number of workdays to a specific date and return a workday (monday-friday).

CREATE FUNCTION [dbo].[fnAddWorkdays]
(
	@StartDate DATETIME,
	@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN
	RETURN	@StartDate
		+ CASE(@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END
		+ @DaysToAdd / 5 * 7
		+ @DaysToAdd % 5
		+ CASE WHEN (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate + CASE (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) - 2)) % 7 + @DaysToAdd % 5 > 4
			THEN 2
			ELSE 0
		END
END

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-24 : 08:52:16

Are you showing the final work day excluding saturday and sunday?


select 
	[dbo].[fnAddWorkdays]('2007-10-21',5),
	[dbo].[fnAddWorkdays]('2007-10-24',5)

Result

                                                                                                              
last_day                                               last_day                                               
------------------------------------------------------ ------------------------------------------------------ 
2007-10-29 00:00:00.000                                2007-10-31 00:00:00.000

Do you think it gives the correct result?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 09:02:39

negative @DaysToAdd ?

SELECT	[20071001 is] 			= DATENAME(weekday, '20071001'), 
	[-1 WD of 20071001] 		= [date], 
	[WEEKDAY of -1 WD of 20071001] 	= DATENAME(weekday, [date])
FROM	
(
	SELECT	[date] = dbo.fnAddWorkdays('20071001', -1)
) d

/*
20071001 is                    -1 WD of 20071001        WEEKDAY of -1 WD of 20071001   
------------------------------ ------------------------ ------------------------------ 
Monday                         2007-09-30 00:00:00.000  Sunday
*/

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 09:52:21

That's a bummer.
But I think that only relates to you when your boss asks you to finish the damn job "yesterday"

E 12°55'05.25"
N 56°04'39.16"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-10-24 : 09:56:17

Your function might be simpler if you use this algorithm to determine the weekday number, because it has no dependency on the setting of datefirst or language.

-- Mon=0,Tue=1,Wed=2,Thu=3,Fri=4,Sat=5,Sun=6
select datediff(dd,-53690,getdate())%7

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-24 : 10:30:46

The @@DATEFIRST variable reverses that effect.

But here are two rewrites

SELECT	@StartDate
	+ CASE DATEDIFF(DAY, '17530101', @StartDate) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END
	+ @DaysToAdd / 5 * 7
	+ @DaysToAdd % 5
	+ CASE DATEDIFF(DAY, '17530101', @StartDate + @DaysToAdd / 5 * 7 + @DaysToAdd % 5 + CASE DATEDIFF(DAY, '17530101', @StartDate) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) % 7
		WHEN 5 THEN 2
		WHEN 6 THEN 1
		ELSE 0
	END

SELECT	@StartDate
	+ CASE DATEDIFF(DAY, '17530101', @StartDate + d.Weekends + d.Leading) % 7
		WHEN 5 THEN 2
		WHEN 6 THEN 1
		ELSE 0
	END
	+ d.Leading
	+ d.Weekends
FROM	(
		SELECT	CASE DATEDIFF(DAY, '17530101', @StartDate) % 7
				WHEN 5 THEN 2
				WHEN 6 THEN 1
				ELSE 0
			END AS Leading,
			@DaysToAdd / 5 * 7 + @DaysToAdd % 5 AS Weekends
	) AS d

E 12°55'05.25"
N 56°04'39.16"

chuckamusprime
Starting Member

1 Post

Posted - 2010-02-12 : 11:59:00

I had to do something similar....here's how i did it...

CREATE FUNCTION [dbo].[fnAddWorkdays]
(
@StartDate DATETIME,
@DaysToAdd INT
)
RETURNS DATETIME
AS
BEGIN

DECLARE @RtnVal DATETIME
DECLARE @Modifier INT
DECLARE @RtnValWeekDay varchar(10)
SET @RtnVal = DATEADD(day,@DaysToAdd,@StartDate)
IF (@DaysToAdd < 0)
SET @Modifier = -1
ELSE
SET @Modifier = 1

SET @RtnValWeekDay = DATENAME(weekday, @RtnVal)

WHILE @RtnValWeekDay IN ('Saturday', 'Sunday')
BEGIN
SET @RtnVal = DATEADD(day,@Modifier,@RtnVal)
SET @RtnValWeekDay = DATENAME(weekday, @RtnVal)
END

RETURN @RtnVal
END

A closed mouth gathers no foot.

Generally speaking, you aren't learning much when your lips are moving.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources