calculating nonholidays/weekends

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)

calculating nonholidays/weekends

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-11-19 : 14:24:44

Update Employee
SET Emp_Comment=Emp_ID+'Enrolled on'+ CONVERT(VARCHAR(10), GETDATE()+3, 101)
FROM Employee
WHERE DATEPART(WEEKDAY, GETDATE()) in ( 2,3,4,5,6)
AND NOT EXISTS ( SELECT 1 FROM HOLIDAYS WHERE convert(varchar, Holiday_Date, 112)= convert(varchar, getdate(), 112))

table should only be updated during workdays and nonholidays.hence this statement.
But i m trying to get "todays + 3 nonholidays's date" .
Suppose today is Wednesday, getdate+3 should be

select getdate(), getdate()+3 should be next week tuesday.

Chandragupta Mourya

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-22 : 01:15:44

do you have a calendar table with defined holidays stored?

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 06:19:54

Hi,

I had a simular problem and I basically created a table for workdays.

One solution is discussed on the MSDN site http://msdn.microsoft.com/en-us/library/aa175781%28SQL.80%29.aspx

OR

The best and easiest to follow solution is below:

http://www.databasejournal.com/features/mssql/article.php/3502256/SQL-Server-Customized-Calendar-Tables.htm

Also, once you have your table, I did a script which looks up the next available workday using the following:


SET	@abDate = (	SELECT TOP	(1)caldate
			FROM		dbo.Calendar
			WHERE		caldate > @abDate 
			AND		workday = '1')

This gives you the next working day. Hope it helps

mattlightbourn
Starting Member

37 Posts

Posted - 2009-11-24 : 06:20:49

remember to DECLARE @abDate or simular, as a DateTime variable and then use the value of the variable as you wish

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources