Become a fan of Slashdot on Facebook

typodupeerror
DEAL: For \$25 - Add A Second Phone Number To Your Smartphone for life! Use promo code SLASHDOT25. Also, Slashdot's Facebook page has a chat bot now. Message it for stories and more. Check out the new SourceForge HTML5 Internet speed test! ×

## Journaljoke_dst's Journal: SQL function to calculate number of work days

This is a follow up on the previous post about easter day, the function getEasterDate is required...

This calculates the number of work days between two dates, using Swedish holidays.

This consists of two functions, one that calculates if a given day is a holiday (isHoliday()) and one that simply loops through the range of days given and checks if any is a holyday (numberOfWorkDays())

If anyone wants to convert this to another country or optimize this one, please tell me about it! I'd love to see it...

Create Function isHoliday(@date as datetime)
returns bit
as begin
if DATEPART(dw, @date) in (1,7)
or (DAY(@date)=1 and MONTH(@date) in (1,5)) --Jan 1, May 1
or (DAY(@date)=6 and MONTH(@date)=6) --6 july, national day
or (MONTH(@date)=12 and DAY(@date) in (25,26)) --xmas
return 1
declare @easter as datetime set @easter = dbo.getEasterDate(YEAR(@date))
if DATEDIFF(day, @easter, @date) in (-2, 1, 39) --easter + ascention
return 1
return 0
end

go

Create function numberOfWorkDays( @fromDate as datetime, @toDate as datetime)
returns int
as begin
declare @day as datetime
declare @endday as datetime
declare @dayCount int

set @day = @fromdate
set @endday = @toDate
set @dayCount = 1

if @endday @day begin
declare @tempday datetime set @tempday=@day
set @day=@endday
set @endday = @tempday
end

while @day @endday begin
if dbo.isHoliday(@day)=0
set @dayCount = @dayCount + 1

set @day = dateadd(day, 1, @day)
end

return @dayCount
end

This discussion has been archived. No new comments can be posted.