PS If there are other such routines that you know are required, then let me know. If I cannot resolve them I am sure I know a person who can!
Thursday, October 06, 2005
Number of working days between 2 dates in TSQL
I had no idea that this piece of code would be generally useful, but apparently there are a lot of requests for this kind of thing so here it is, for anybody else that finds it of value, please use it freely in your own stored procedures or functions, with appropriate testing of course.
PS If there are other such routines that you know are required, then let me know. If I cannot resolve them I am sure I know a person who can!
PS If there are other such routines that you know are required, then let me know. If I cannot resolve them I am sure I know a person who can!
2 comments:
I found a nice way to achieve the same result without a WHILE loop:
-----------------------------------------------------
SET DATEFIRST 1 /* Monday */
SELECT DATEDIFF(DAY, @stateDate, @endDate)
- (DATEDIFF(WEEK, @stateDate, @endDate) * 2) /* Remove weekends. (Note: DATEDIFF appears to ignore DATEFIRST. Every Sunday is a new week.) */
- (CASE WHEN (DATEPART(WEEKDAY, @stateDate) = 7 /* Sun */) THEN 1 ELSE 0 END) /* Remove the startDate Sunday */
+ (CASE WHEN (DATEPART(WEEKDAY, @endDate) = 7 /* Sun */) THEN 1 ELSE 0 END) /* Add back the extra Sunday that was removed by line 2 */
Nice code! I would do it the same.
Post a Comment