Thursday 9 June 2011

SQL Server: DateDiff with Weekdays

I needed a simple way to work out the number of weekdays between two dates for a report (not worried about holidays).

The native datediff function in T-SQL works with calendar days, so I needed a custom function. I lifted this off a forum and modified it a bit:


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fnDateDiffWeekdays]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fnDateDiffWeekdays]
GO

/*
=============================================
Description: Calculate datediff for weekdays
Usage: select dbo.fnDateDiffWeekdays('2011-05-09 09:04:22.593','2011-06-09 09:04:22.593')
=============================================
*/

CREATE function dbo.fnDateDiffWeekdays
(
@fromdate datetime,
@todate datetime
)
RETURNS int
AS
begin
declare @procdate datetime, @enddate datetime
declare @weekdays int
set @procdate = @fromdate
set @weekdays = 0

while (@procdate < @todate)
begin
if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7)

set @weekdays = @weekdays + 1
set @procdate = dateadd(d, 1, @procdate)

end
---
if @todate is null
set @weekdays = null

return @weekdays
end



Worked fine in my testing!