count working days b/w two dates

Question

0.00/5 (No votes)

See more:

i have a code to count working days between two dates, it exclude saturdays and sundays as a holiday but i want to exclude only sundays as a holiday please help me its urgent.

here is code for excluding sunday's and saturdays

SQL

ALTER FUNCTION [dbo].[GetWorkingDaysBWDates]
(
    @dt1 datetime,
    @dt2 datetime
)
RETURNS decimal
AS
BEGIN

    declare @Result int

    declare @WeekEnd int
    declare @wdf int
    declare @wdl int
    declare @fullWeek int

    set  @Result = 0
    select  @WeekEnd =  DATEPART ( dw , '02-04-2012')

    if(@dt1 > @dt2)
    begin
        RETURN @Result
    end


    select @wdf = @WeekEnd -  DATEPART ( dw , @dt1)

    if(@wdf > 5)
    begin
    set @wdf = 5
    end

    select @wdl =  DATEPART ( dw , @dt2)

    if(@wdl > 5)
    begin
    set @wdl = 5
    end

    Select @dt1 =  DATEADD(day,@WeekEnd - DATEPART (dw , @dt1)+1  ,@dt1)

    Select @dt2 =  DATEADD(day,-1 * DATEPART (dw , @dt2) + 1 ,@dt2)

    select  @fullWeek= (datediff(day, @dt1, @dt2))/7

    select @Result = (@fullWeek * 5) + @wdf + @wdl -1

    RETURN @Result

END

Posted 4-Mar-12 19:43pm

IramAtteq

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Answer 1 · 2012-03-04T19:59:00

Solution 1

Try

SQL

if(@wdl > 6)
    begin
    set @wdl = 6
    end

Posted 4-Mar-12 19:59pm

Abhinav S

Comments

IramAtteq 5-Mar-12 2:03am

no i tried it but it did'nt worked

Prasad_Kulkarni · Answer 2 · 2012-03-04T20:08:00

When dealing with day-of-week calculations, it's important to take account of the current DATEFIRST settings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

SQL

SELECT *
FROM your_table
WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)

count working days b/w two dates

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

count working days b/w two dates

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us