SQL - WHERE clause Return results with next working day

Question

Hi, I seem to be asking a lot of SQL questions at the moment. (I would normally write a program to sort the data into a report table, however at the moment that is not possible, and needs to be done using SQL)

The Question I need a where clause that returns results with the next working day. i.e. Monday 01/01/01 the next working day would be Tuesday 02/01/01 which could be achieved with a simple date add. However on a Friday 05/01/01 the next working day is Monday 08/01/01. Is there anything built in to help cope with this easily?

Thanks for your advice.

Answer 1

The key is to use the DATEPART(weekday,@date) function, it'll return the day of the week, so if it's saturday or sunday you just add one or two to the current date to get the desired result.

You can create a user defined function to do so easily, for example Pinal Dave has this

CREATE FUNCTION dbo.udf_GetPrevNextWorkDay (@dtDate DATETIME, @strPrevNext VARCHAR(10))
RETURNS DATETIME
AS
BEGIN
DECLARE @intDay INT
DECLARE @rtResult DATETIME
SET @intDay = DATEPART(weekday,@dtDate)
--To find Previous working day
IF @strPrevNext = 'Previous'
IF @intDay = 1
SET @rtResult = DATEADD(d,-2,@dtDate)
ELSE
IF @intDay = 2
SET @rtResult = DATEADD(d,-3,@dtDate)
ELSE
SET @rtResult = DATEADD(d,-1,@dtDate)
--To find Next working day
ELSE
IF @strPrevNext = 'Next'
IF @intDay = 6
SET @rtResult = DATEADD(d,3,@dtDate)
ELSE
IF @intDay = 7
SET @rtResult = DATEADD(d,2,@dtDate)
ELSE
SET @rtResult = DATEADD(d,1,@dtDate)
--Default case returns date passed to function
ELSE
SET @rtResult = @dtDate
RETURN @rtResult
END
GO

Answer 2

You could do this with a simple day check and add 3 days rather than one if its a Friday. However, do you need to take into consideration public holidays though?

Answer 3

You could do this with a simple case statement.

select case when datepart(dw, getdate()) >= 6 then getdate() + (9 - datepart(dw, getdate())) else getdate() + 1 end

Answer 4

CREATE FUNCTION dbo.uf_GetNextWorkingDay (@givenDate DATETIME)
RETURNS DATETIME
AS
BEGIN

    DECLARE @workingDate DATETIME

    IF (DATENAME(dw , @givenDate) = 'Friday')
     BEGIN
       SET @workingDate = DATEADD(day, 3, @givenDate)
     END
    ELSE IF (DATENAME(dw , @givenDate) = 'Saturday')
     BEGIN
       SET @workingDate = DATEADD(day, 2, @givenDate)
     END
    ELSE 
     BEGIN
       SET @workingDate = DATEADD(day, 1, @givenDate)
     END

    RETURN @workingDate
END

A good article http://ryanfarley.com/blog/archive/2005/02/14/1685.aspx

Answer 5

What you need is a calendar table. Getting the next working days is not so simple if you need to account for holidays other than the weekend. The table basically contains just two columns Date and an integer field indicating whether it is a working/non working day- but there can be other columns for - example quarter etc as well.

This table is populated once a year and then maintained as necessary. Getting the result for next working day then becomes as simple as a query like this.

SELECT field1,filed2 from your table T where your date_Field = (SELECT min(date) From calendar table where WorkingDay = 1 and date > GetDate())

/P

Answer 6

How about somethin like this?

select * from table
where (date = dateadd(dd,1,@today) and datepart(weekday,@today) not in (6,0) ) --its not friday or saturday 
or    (date = dateadd(dd,2,@today) and datepart(weekday,@today) = 0) -- its saturday 
or    (date = dateadd(dd,3,@today) and datepart(weekday,@today) = 6) --its friday

the date attribute should have the same time as @today or else you have to also use between