CODEWITHSUNDEEP
sqlsql-server
Mandy
Mandy

Reputation: 87

Declare date, then add next business day

I'm not getting the results that I need.

All I want to do is declare my date and then I want add the next business day to it. ie. @CASHDATE+1 (this works) but I need the next business day being 5th.

DECLARE  @CASHDATE DATETIME
SET @CASHDATE='2016-12-02'

SELECT <<COLUMN_NAME>>  FROM <<TABLE_NAME>> 
WHERE RECEIVED_DATE = @CASHDATE /*'2016-12-02'*/ 
AND TRANS_POST_DATE = @CASHDATE+1 /*'2016-12-05'*/) <== I don't want it to be '2016-12-03 or 04', I'm trying to get the next business day.

Any ideas on how to achieve this would be welcome. I've been experimenting with DATEPART(dw, @CASHDATE) NOT IN (1, 7) but I just can't get the result I want.

Upvotes: 2

Views: 7165

Answers (3)

Cristina Carrasco
Cristina Carrasco

Reputation: 713

Use the command DATEADD:

AND TRANS_POST_DATE = DATEADD (dd, 1, @CASHDATE)--@CASHDATE+1

DATEADD (Transact-SQL)

make a function; It's easy... gime a minute... to make it

-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
/*

declare @date datetime 
set @date = '2016-12-10'

declare @newDate datetime
select  @newDate = dbo.[GetBussinesDay](@date)

select @date, @newDate 

*/
create FUNCTION [dbo].[GetBussinesDay]
(
    @Date as datetime
)
RETURNS datetime
AS
BEGIN
    declare @NewDate datetime

    declare @BussinessDays table ( bDay int)
    insert into @BussinessDays VALUES (2) --mon
    insert into @BussinessDays VALUES (3) --Tue
    insert into @BussinessDays VALUES (4) --Wed
    insert into @BussinessDays VALUES (5) --Thu
    insert into @BussinessDays VALUES (6) --Fri

    if exists(select bDay from @BussinessDays where bDay = DATEPART(dw,@Date))
    begin
        set @NewDate = @Date;
    end
    else
    begin
        set @NewDate = dbo.GetBussinesDay(dateadd(dd,1,@Date));
    end

    return @NewDate

END

Create the function and change your code:

AND TRANS_POST_DATE =  dbo.GetBussinesDay( @CASHDATE)--@CASHDATE+1

Also, you can change the bussines days in the function... for futures changes or rules.

Upvotes: 1

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can make use of the DATEDIFF and DATEADD function:

DECLARE @CASHDATE datetime='2016-12-03' 
SELECT
    CashDate = @CASHDATE,
    NextBusinessDay = 
        DATEADD(DAY,
                CASE 
                    WHEN DATEDIFF(DAY, 0, @CASHDATE) % 7 > 3 
                        THEN 7 - DATEDIFF(DAY, 0, @CASHDATE) % 7 
                    ELSE 1 
                END,
                @CASHDATE
        )

The DATEDIFF(DAY, 0, @CASHDATE) % 7 determines what day @CASHDATE is. Based on that, add the necessary number of days up to the next Monday.

Upvotes: 0

Matt
Matt

Reputation: 14361

DATEADD(day,
    CASE
       WHEN DATENAME(dw,CASHDATE) = 'Friday' THEN 3
       WHEN DATENAME(dw,CASHDATE) = 'Saturday' THEN 2
       ELSE 1
    END,
    CASHDATE)

Here is an example to show you how it would calculate

DECLARE @Dates AS TABLE (CASHDATE DATETIME)
INSERT INTO @Dates VALUES ('2016-11-28'),('2016-11-29'),('2016-11-30'),('2016-12-01'),('2016-12-02'),('2016-12-03'),('2016-12-04')

;WITH cteNextBizDays AS (
    SELECT
       CASHDATE
       ,NextBusinessDay = DATEADD(day,
          CASE
             WHEN DATENAME(dw,CASHDATE) = 'Friday' THEN 3
             WHEN DATENAME(dw,CASHDATE) = 'Saturday' THEN 2
             ELSE 1
          END
          ,CASHDATE)
    FROM
       @Dates
)

SELECT
    CASHDATE
    ,DATENAME(dw,CASHDATE) CashDateDayOfWeek
    ,NextBusinessDay
    ,DATENAME(dw,NextBusinessDay) NextBusinessDayDayofWeek
FROM
    cteNextBizDays

One of the considerations you need to think about when using DATEPART(dw,Date) is what the DATEFRIST setting is. By Comparing with DATENAME you don't have to worry about that.

Upvotes: 4

Related Questions