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 2008 Forums
 Transact-SQL (2008)
 Datediff to exclude weekends

Author  Topic 

sonjan
Starting Member

22 Posts

Posted - 2013-02-05 : 18:03:34
Is it possible to calculate difference in days between dates that exclude weekends, then calculate 8 hour day by hourly rate eg

start date: 28/06/2012 end date: 25/07/2012. Actual difference in days is 27, but remove the weekends (8 days) = 19 days. The actual duration in hours between start and end dates = 648 but should be between 7.00am - 3.00pm (8 hours). Hourly rate = $5.00.

End result should be: 19 days x 8 hours = 152 hours x $5.00 = $760.00

Any help would be much appreciated. Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-05 : 18:57:36
[code]5.00*8*( DATEDIFF(dd,'20120628','20120725')-2*DATEDIFF(wk,'20120628','20120725') )[/code]
Go to Top of Page

sonjan
Starting Member

22 Posts

Posted - 2013-02-12 : 22:20:51
Thanks James - this is great,much appreciated! Apology for delay.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 04:48:47
see

http://visakhm.blogspot.in/2010/03/calculating-business-hours.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -