|
SQL Server T-SQL User-Defined Function of the WeekCalculating the Weekdays Between Two DatesVolume 1 Number 3 December 6, 2002 (Bonus Issue) Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm Visit http://www.NovickSoftware.com for additional material about SQL Server, Visual Basic, ASP, XML, .Net and security. Visit the T-SQL UDF of the Week Archives at: http://www.NovickSoftware.com/UdfOfWeek/UDFofWeekArchive.htm Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com When you want to know the number of days between two dates, the built-in function DATEDIFF does the trick. But sometimes you need to know the number of weekdays between two dates. That turns out to be somewhat harder. None of the built-in functions will do the job. One way to compute weekdays between two dates is to use a table with a row for every date and columns that designate it as a weekday and possibly as a holiday. This solution works great and it works particularly well when you have several different calendars. However, for weekdays it isn't necessary. This issues's UDF, udf_DT_WeekdaysBtwn, computes the week days between two dates. Here's the script that creates it: /------------- Copy From Below this line ----------------------\ CREATE FUNCTION dbo.udf_DT_WeekdaysBtwn ( @dtStart datetime -- Day we want to start counting from , @dtEnd datetime -- Day we count until ) RETURNS INT -- Number of Week days between two dates. AS BEGIN /*Computes the number of weekdays between two dates. Does not * account for holidays, They're counted if they're M-F. It * counts only one of the end days. So if the dates are the same * the result is zero. Order does not matter. Will swap dates * if @dtEnd < @dtStart. * * This function is sensitive to the setting of @@DATEFIRST. Any * value other than 7 (the default) will make the results * incorrect. A test for this will cause the function to re * * Related Functions: udf_DateBusinessdaysBetween * * Common Usage: select dbo.udf_DT_WeekdaysBtwn ('2002-07-01', '2002-07-05') * * Test: PRINT 'Test 1 7/1-7/5 ' + case when 4= dbo.udf_DT_WeekdaysBtwn ('2002-07-01', '2002-07-05') then 'Worked' else 'ERROR' END PRINT 'Test 2 11/27-12/5 ' + case when 6= dbo.udf_DT_WeekdaysBtwn ('2002-11-27', '2002-12-05') then 'Worked' else 'ERROR' END * © Copyright 2002 Andrew Novick. You may use this function * in your databases, including databases that you sell. You may * not publish this function, either in print or electronically. ****************************************************************/ if @@DATEFIRST <> 7 return NULL -- 7 is the default. -- Days are broken into 3 parts for calculation. -- 1) The remaining weekdays in the week with the start date -- 2) The weekdays in the full weeks between the start and end -- 3) the weekdays in the week with the end date. -- The three are totaled for the result. DECLARE @nFullWeeks int -- complete weeks between , @nFullWeekDays int -- Days in Full Weeks , @nStartWeekDays int -- Days in the starting week , @nEndWeekDays int -- Days in the ending week , @dtSWap datetime -- for switching end and start dates -- First a test for End before Start, Swap Dates if needed if @dtEnd < @dtStart BEGIN -- swap SET @dtSWAP = @dtEnd SET @dtEnd = @dtStart Set @dtStart = @dtSwap END -- Number of days in the week of @dtStart SELECT @nStartWeekDays = 7 - datepart(dw, @dtStart) - CASE WHEN 7= datepart(dw, @dtStart) THEN 0 ELSE 1 END -- Calculate the number of full weeks select @nFullWeeks = datediff( week, @dtStart, @dtEnd) - 1 -- -1 for current week -- calculate the number of days in these full wks. select @nFullWeekDays = @nFullWeeks * 5 -- Finally, the number of days in the week with the end date. SELECT @nEndWeekDays = datepart(dw, @dtEnd) - -- take off to -- adjust for wkend CASE WHEN datepart( dw, @dtEnd) = 7 THEN 2 ELSE 1 END -- Sum everything together and return it RETURN @nFullWeekDays + @nStartWeekDays + @nEndWeekDays END GO GRANT EXECUTE ON udf_DT_WeekdaysBtwn to PUBLIC GO \------------ Stop copying above this line --------------------/ Let's test it out: /------------- Copy From Below this line ----------------------\ select dbo.udf_DT_WeekdaysBtwn ('2002-11-27', '2002-12-06') as [11/27-12/6 Fri] , dbo.udf_DT_WeekdaysBtwn ('2002-11-27', '2002-12-07') as [11/27-12/7 Sat] , dbo.udf_DT_WeekdaysBtwn ('2002-11-27', '2002-12-08') as [11/27-12/8 Sun] , dbo.udf_DT_WeekdaysBtwn ('2002-11-27', '2002-12-09') as [11/27-12/9 Mon] GO \------------ Stop copying above this line --------------------/ (Results) 1/27-12/6 Fri 11/27-12/7 Sat 11/27-12/8 Sun 11/27-12/9 Mon -------------- -------------- -------------- -------------- 7 7 7 8 As you can see, the first weekend, 11/30 and 12/1, isn't counted. Saturday and Sunday aren't counted, but Monday is. +--------------------------------------------------------------+ Do you have a great UDF that you'd like to share? Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Please send them to: UDF@NovickSoftware.com and they might be published in this newsletter. I try and respond to every request that I get. Thanks, Andrew Novick +--------------------------------------------------------------+ This newsletter is published by Novick Software http://NovickSoftware.com Copyright (c) 2002 Novick Software. All rights reserved.
|
|
