Diff of days ( in buisness days)

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)

Diff of days ( in buisness days)

Author

Topic

niranjankumark
Posting Yak Master

164 Posts

Posted - 2008-11-06 : 06:13:56

In datepart difference of days can be taken.How to take the difference of business days ( saturday and sunday should not be in count )

ex : Nov-1-2008 , nov-30-2008 between this 2 dates saturday and sunday should not be included. buisness days is only 20.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-11-06 : 06:32:29

did you do any searching before posting this question?

2 seconds on the search engine of your choice would have given you many answers. One of them:

http://classicasp.aspfaq.com/date-time-routines-manipulation/how-do-i-count-the-number-of-business-days-between-two-dates.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-06 : 09:03:35

[code]CREATE FUNCTION [dbo].[fnWorkHours]
(
@StartDate DATETIME,
@EndDate DATETIME)
RETURNS FLOAT
AS
BEGIN
RETURN CAST(
(DATEDIFF(DAY, @StartDate, @EndDate) + 1)
- (DATEDIFF(WEEK, @StartDate, @EndDate) * 2)
- (CASE WHEN DATENAME(WEEKDAY, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
- (CASE WHEN DATENAME(WEEKDAY, @EndDate) = 'Saturday' THEN 1 ELSE 0 END)
- CASE WHEN DATENAME(WEEKDAY, @StartDate) IN ('Saturday', 'Sunday') THEN 0
ELSE CAST(CONVERT(CHAR(12), @StartDate, 114) AS DATETIME) END
- CASE WHEN DATENAME(WEEKDAY, @EndDate) IN ('Saturday', 'Sunday') THEN 0
ELSE 1.0 - CAST(CONVERT(CHAR(12), @EndDate, 114) AS DATETIME) END
AS FLOAT) * 24
END[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources