CODEWITHSUNDEEP
sql-servert-sql
Nate Pet
Nate Pet

Reputation: 46222

SQL Server Check if day does not fall on weekend and if so, iterate to a weekday

I am using SQL Server with t-Sql I have the following code that checks to see if a date falls on a weekend and if it does, it will iterate until the day falls on a weekday

    Declare @ProDate as Date
    set @ProDate = '08/05/12'

    WHILE (DATEPART(DW, @ProDate) =  1 OR DATEPART(DW, @ProDate) =  7 )
    BEGIN

      set @ProDate =  DATEADD(day, 1, @ProDate)

    END

    select @ProDate

The code seems to work. Wondering if I missed anything or if there is a better way to handle this.

Upvotes: 3

Views: 4274

Answers (4)

Kundan
Kundan

Reputation: 1

USE below to exclude weekeends and Holiday

Declare @AddDay as integer = 3
Declare @NextWorkingDate  DateTime
Declare @StartDate  DateTime = Cast(getdate() as date)

While  @AddDay > 0 
    begin

        Select @NextWorkingDate =  @StartDate + @AddDay +
        (datediff(wk, @StartDate, @StartDate+ @AddDay  ) * 2) -- add weekend 

        --Exclude weekend
        If datepart(dw,@NextWorkingDate ) = 1 or datepart(dw,@NextWorkingDate ) = 7  --Add 2 days if target date is either Saturday or Sunday
            set @NextWorkingDate = @NextWorkingDate + 2 

        --Count no of holidays if falling within Hold days/Deposit days
        Select @AddDay = Count(*)  from HolidayTable ST --Holiday list
                    where ST.OffDate between @StartDate+1 and @NextWorkingDate
        Set @StartDate = @NextWorkingDate
    End         

Select @NextWorkingDate

Upvotes: 0

Kundan
Kundan

Reputation: 1

Use below code to get next wrking date after excluding weekends and Holidays

Declare @AddDay as integer = 3
Declare @NextWorkingDate  DateTime
Declare @StartDate  DateTime = Cast(getdate() as date)

While  @AddDay > 0 
    begin

        Select @NextWorkingDate =  @StartDate + @AddDay +
        (datediff(wk, @StartDate, @StartDate+ @AddDay  ) * 2) -- add weekend 

        --Exclude weekend
        If datepart(dw,@NextWorkingDate ) = 1 or datepart(dw,@NextWorkingDate ) = 7  --Add 2 days if target date is either Saturday or Sunday
            set @NextWorkingDate = @NextWorkingDate + 2 

        --Count no of holidays if falling within start date and nextwrking date
        Select @AddDay = Count(*)  from HolidayTable ST --Holiday list
                    where ST.OffDate between @StartDate+1 and @NextWorkingDate
        Set @StartDate = @NextWorkingDate
    End         

Select @NextWorkingDate

Upvotes: 0

podiluska
podiluska

Reputation: 51494

This code is dependent on the setting of DATEFIRST in your system.

I'd add a SET DATEFIRST 7 before the date checks

Alternately, this avoids the while loop

declare @df int = @@Datefirst       
set datefirst 1     
select 
    case when DATEPART(DW, @ProDate)>=6 then 
        DATEADD(d, 8-DATEPART(DW, @ProDate), @prodate)
    else @ProDate
    end    
set DATEFIRST @df

Upvotes: 3

Taryn
Taryn

Reputation: 247650

This code will work. It is almost identical to code that we use in a heavily used function.

The only suggestion that I might have is do you need to integrate a Holiday check? We have a Holiday table to store dates that need to be skipped as well.

Upvotes: 0

Related Questions