How to Get Previous Business Day in a Week with That of Current Business Day Using SQL Server

How to get Previous business day in a week with that of current Business Day using sql server

SELECT  DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
                        WHEN 'Sunday' THEN -2 
                        WHEN 'Monday' THEN -3 
                        ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))

I prefer to use DATENAME for things like this over DATEPART as it removes the need for Setting DATEFIRST And ensures that variations on time/date settings on local machines do not affect the results. Finally DATEDIFF(DAY, 0, GETDATE()) will remove the time part of GETDATE() removing the need to convert to varchar (much slower).

---

EDIT (almost 2 years on)

This answer was very early in my SO career and it annoys me everytime it gets upvoted because I no longer agree with the sentiment of using DATENAME.

A much more rubust solution would be:

SELECT  DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                        WHEN 1 THEN -2 
                        WHEN 2 THEN -3 
                        ELSE -1 
                    END, DATEDIFF(DAY, 0, GETDATE()));

This will work for all language and DATEFIRST settings.

Select the previous business day by using SQL

SELECT ..
.
.
AND DATE(a.scantime) = (CASE WEEKDAY(CURRENT_DATE) 
                             WHEN 0 THEN SUBDATE(CURRENT_DATE,3)
                             WHEN 6 THEN SUBDATE(CURRENT_DATE,2) 
                             WHEN 5 THEN SUBDATE(CURRENT_DATE,1)
                             ELSE SUBDATE(CURRENT_DATE,1) 
                        END)
..
..

Getting SQL to return data ONLY for the last business day

Your method is fine. Your variable definition has DAY, in it. So:

SET @CREATIONDAY = DATEADD(DAY,
                           (CASE DATENAME(WEEKDAY, convert(date,GETDATE()))
                              WHEN 'Sunday' THEN -2 
                              WHEN 'Monday' THEN -3 
                              ELSE -1
                            END),
                           GETDATE());

Or, just add the logic into the WHERE clause:

WHERE (DATENAME(WEEKDAY, GETDATE()) = 'Sunday' AND
       CREATION_DATE = DATEADD(DAY, -2, CAST(GETDATE as DATE))
      ) OR
      (DATENAME(WEEKDAY, GETDATE()) = 'Monday' AND
       CREATION_DATE = DATEADD(DAY, -3, CAST(GETDATE as DATE))
      ) OR
      (DATENAME(WEEKDAY, GETDATE()) = 'Sunday' AND
       CREATION_DATE = DATEADD(DAY, -2, CAST(GETDATE as DATE))
      ) OR
      (DATENAME(WEEKDAY, GETDATE()) NOT IN ('Sunday', 'Monday') AND
       CREATION_DATE = DATEADD(DAY, -1, CAST(GETDATE as DATE))
      ) 

Keep last n business days records from today date in SQL Server

You can get the 7th working day from today as

  select top(1) cast(dateadd(d, -n + 1, getdate()) as date) d
  from (
    select  n
       , sum (case when datename(dw, dateadd(d, -n + 1, getdate())) not in ('Sunday',  'Saturday') then 1 end) over(order by n) wdn
    from (
       values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11)
    )t0(n)
  ) t
  where wdn = 7
  order by n;

Generally using on-the-fly tally for a @n -th day

declare @n int = 24;

with t0(n) as (
  select n 
  from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
  ) t(n)
), tally as (
     select top(@n + (@n/5 +1)*2) row_number() over(order by t1.n)  n
     from t0 t1, t0 t2, t0 t3
)
select top(1) cast(dateadd(d, -n + 1, getdate()) as date) d
from (
  select  n
     , sum (case when datename(dw, dateadd(d, -n + 1, getdate())) not in ('Sunday',  'Saturday') then 1 end) over(order by n) wdn
  from  tally
) t
where wdn = @n
order by n;

Need a function to return date of next/Previous business day SQL Server

You have to create a table type variable first:

CREATE TYPE BusinessDateTableType AS TABLE 
( 
   [WeekDay] VARCHAR(50),
   IsBusinessDate BIT 
);

Then create the function that takes a table-valued parameter of the above type:

CREATE FUNCTION UDF_GetNextBusinessDay
(    
    @businessDates BusinessDateTableType READONLY, 
    @type VARCHAR(10),
    @day DATE
)
RETURNS DATE
AS
BEGIN
    -- Declare the return variable here
    DECLARE @nextBusinessDate DATE

    ;WITH cte AS (
        SELECT CASE 
                WHEN @type = 'Next' THEN 1 
                WHEN @type = 'Previous' THEN -1
               END AS i
        UNION ALL
        SELECT CASE 
                WHEN @type = 'Next' THEN i + 1 
                WHEN @type = 'Previous' THEN i -1
               END AS i
        FROM cte
        WHERE ABS(i) < 7
    )                                                                 
    SELECT TOP 1 @nextBusinessDate = DATEADD(day, i, @day)
    FROM cte AS d1
    INNER JOIN @businessDates AS d2 ON DATENAME(DW, DATEADD(day, i, @day)) = d2.WeekDay
    WHERE d2.IsBusinessDate = 1           
    ORDER BY ABS(i)                  

    -- Return the result of the function
    RETURN @nextBusinessDate
END

EDIT:

We can easily substitute the table-type variable in the UDF with seven BIT type variables, then use a table variable inside the UDF and populate it with the values of these variables:

CREATE FUNCTION UDF_GetNextBusinessDay2
(    
    @IsMonWorkingDay BIT,
    @IsTueWorkingDay BIT,
    @IsWedWorkingDay BIT,
    @IsThuWorkingDay BIT,
    @IsFriWorkingDay BIT,
    @IsSatWorkingDay BIT,
    @IsSunWorkingDay BIT,
    @type VARCHAR(10),
    @day DATE
)
RETURNS DATE
AS
BEGIN
    -- Declare the return variable here
    DECLARE @nextBusinessDate DATE

    DECLARE @businessDates TABLE ([WeekDay] VARCHAR(50), IsBusinessDate BIT) 

    INSERT INTO @businessDates VALUES
    ('Monday', @IsMonWorkingDay),
    ('Tuesday', @IsTueWorkingDay),
    ('Wednesday', @IsWedWorkingDay),
    ('Thursday', @IsThuWorkingDay),
    ('Friday', @IsFriWorkingDay),
    ('Saturday', @IsSatWorkingDay),
    ('Sunday', @IsSunWorkingDay)

    ;WITH cte AS (
        SELECT CASE 
                WHEN @type = 'Next' THEN 1 
                WHEN @type = 'Previous' THEN -1
               END AS i
        UNION ALL
        SELECT CASE 
                WHEN @type = 'Next' THEN i + 1 
                WHEN @type = 'Previous' THEN i -1
               END AS i
        FROM cte
        WHERE ABS(i) < 7
    )                                                                 
    SELECT TOP 1 @nextBusinessDate = DATEADD(day, i, @day)
    FROM cte AS d1
    INNER JOIN @businessDates AS d2 ON DATENAME(DW, DATEADD(day, i, @day)) = d2.WeekDay
    WHERE d2.IsBusinessDate = 1           
    ORDER BY ABS(i)                  

    -- Return the result of the function
    RETURN @nextBusinessDate
END

Using the second version of the UDF with this test data:

DECLARE @type VARCHAR(10) = 'Next'
DECLARE @day DATE = '2014-12-22'
DECLARE @nextBusinessDate DATE

SET @nextBusinessDate = dbo.UDF_GetNextBusinessDay2(1,0,0,0,0,0,1, @type, @day)
SELECT @nextBusinessDate

produces the following result:

2014-12-28

How can I set last business date in SQL Server

declare @LastBizDate as datetime
set @LastBizDate = CASE DATENAME(WEEKDAY, GETDATE())
                      WHEN 'Monday' THEN GETDATE()-3
                      WHEN 'Sunday' THEN GETDATE()-2
                      ELSE GETDATE()-1 END
PRINT @LastBizDate

Add business days to date in SQL without loops

This answer has been significantly altered since it was accepted, since the original was wrong. I'm more confident in the new query though, and it doesn't depend on DATEFIRST

---

I think this should cover it:

declare @fromDate datetime
declare @daysToAdd int

select @fromDate = '20130123',@DaysToAdd = 4

declare @Saturday int
select @Saturday = DATEPART(weekday,'20130126')

;with Numbers as (
    select 0 as n union all select 1 union all select 2 union all select 3 union all select 4
), Split as (
    select @DaysToAdd%5 as PartialDays,@DaysToAdd/5 as WeeksToAdd
), WeekendCheck as (
    select WeeksToAdd,PartialDays,MAX(CASE WHEN DATEPART(weekday,DATEADD(day,n.n,@fromDate))=@Saturday THEN 1 ELSE 0 END) as HitWeekend
    from
    Split t
        left join
    Numbers n
        on
            t.PartialDays >= n.n
group by WeeksToAdd,PartialDays
)
select DATEADD(day,WeeksToAdd*7+PartialDays+CASE WHEN HitWeekend=1 THEN 2 ELSE 0 END,@fromDate)
from WeekendCheck

We split the time to be added into a number of weeks and a number of days within a week. We then use a small numbers table to work out if adding those few days will result in us hitting a Saturday. If it does, then we need to add 2 more days onto the total.

Finding the first and last business day for every month sql

If you got already a table with all the weekdays only:

select min(datecol), max(datecol)
from BusinessOnlyCalendar
group by year(datecol), month(datecol)

But you should expand your calendar to include all those calculations you might do on date, like FirstDayOfWeek/Month/Quarter/Year, WeekNumber, etc.

When you got a column in your calendar indicating business day yes/no, it's a simple:

select min(datecol), max(datecol)
from calendar
where businessday = 'y'
group by year(datecol), month(datecol)

How to query last 2 business days only

select * 
from LetterGenerationTemplateRequest
where createddate >= (
  getdate() -
  case datename(dw,getdate())
  when 'Tuesday' then 5
  when 'Monday' then 4
  else 3
  end 
)  
--and datename(dw,createdDate) not in ('Saturday','Sunday',datename(dw,getdate()))
and datename(dw,createdDate) not in ('Saturday','Sunday')

;  

---

Related Topics