Follow Slashdot stories on Twitter

 



Forgot your password?
typodupeerror

Slashdot videos: Now with more Slashdot!

  • View

  • Discuss

  • Share

We've improved Slashdot's video section; now you can view our video interviews, product close-ups and site visits with all the usual Slashdot options to comment, share, etc. No more walled garden! It's a work in progress -- we hope you'll check it out (Learn more about the recent updates).

×
User Journal

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

Journal by joke_dst

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.

SQL function to calculate number of work days

Comments Filter:

He: Let's end it all, bequeathin' our brains to science. She: What?!? Science got enough trouble with their OWN brains. -- Walt Kelly

Working...