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

Question

i have an ssis Package which runs on business days (mon-Fri). if i receive file on tuesday , background(DB), it takes previous business day date and does some transactions. If i run the job on friday, it has to fetch mondays date and process the transactions.

i have used the below query to get previous business date

Select Convert(varchar(50), Position_ID) as Position_ID,
       TransAmount_Base,
       Insert_Date as InsertDate
  from tblsample
 Where AsOfdate = Dateadd(dd, -1, Convert(datetime, Convert(varchar(10), '03/28/2012', 101), 120))
Order By Position_ID

if i execute this query i'll get the results of yesterdays Transactios. if i ran the same query on monday, it has to fetch the Fridays transactions instead of Sundays.

GarethD · Accepted Answer · 2014-02-14T15:30:03.073

69

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.

edited Feb 14 '14 at 15:30

answered Mar 29 '12 at 09:53

GarethD

68,045
10
83
123

Using `DATENAME` to avoid needing to know `DATEFIRST`, yet completing ignoring that it's language specific? – Damien_The_Unbeliever Mar 29 '12 at 13:30
Very good point. However, I have come across more scenarios where DATEFIRST conflicts have been an issue than scenarios where language conflicts have been an issue. For example, if you were to create a UDF in your database you could not define date first within the UDF, you would be reliant on the query calling the function to set the correct datefirst (or not setting a different datefirst), it is more likely a user will need to set a different datefirst than set a different language (in my experience). – GarethD Mar 29 '12 at 14:10
1

To elaborate further, I used to work in a company where the commission week runs wednesday to tuesday for Paper sales, and Friday to Thurday for Telesales, We could use the same queries to report on this by setting datefirst accordingly, this would have rendered any use of DATEPART(DAY, [date]) to identify weekends useless. This is why I **prefer** to use datename. I do not advocate that it is flawless. – GarethD Mar 29 '12 at 14:13
HI, i sorted the issue by selecting Maximum date from the AsofDate column. So every time it need not check whether the prior business day is friday or any other day. Select Convert(varchar(50),Position_ID) as Position_ID,TransAmount_Base, Insert_Date as InsertDate from BLE..tblIncome_Staging Where AsOfdate = (select max(AsofDate) from tblsample ) Order By Position_IDthis was the logic i implemented. – 0537 Mar 29 '12 at 14:38
1

Why do you need to write DATEDIFF(DAY, 0, GETDATE()) at the end, instead of GETDATE()? – Sheen Nov 17 '16 at 14:45
@Sheen This simply removes the time component from `GETDATE()`, without this it would return the previous working day + the time that the query was run. – GarethD Nov 17 '16 at 14:52
Oh I see. That is why I didn't spot the diff because my code uses only date – Sheen Nov 17 '16 at 14:55

Dale Kilian · Answer 2 · 2019-04-30T11:56:51.013

This function returns last working day and takes into account holidays and weekends. You will need to create a simple holiday table.

-- =============================================
-- Author:      Dale Kilian
-- Create date: 2019-04-29
-- Description: recursive function returns last work day for weekends and 
-- holidays
-- =============================================
ALTER FUNCTION dbo.fnGetWorkWeekday
(
    @theDate DATE
)
RETURNS DATE
AS
BEGIN

DECLARE @importDate DATE = @theDate
DECLARE @returnDate DATE
--Holidays
IF EXISTS(SELECT 1 FROM dbo.Holidays WHERE isDeleted = 0 AND @theDate = Holiday_Date)
BEGIN
SET @importDate = DATEADD(DAY,-1,@theDate);
SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Satruday
IF(DATEPART(WEEKDAY,@theDate) = 7)
BEGIN
    SET @importDate = DATEADD(DAY,-1,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END
--Sunday
IF(DATEPART(WEEKDAY,@theDate) = 1)
BEGIN
    SET @importDate = DATEADD(DAY,-2,@theDate);
    SET @importDate = (SELECT dbo.fnGetWorkWeekday(@importDate))
END


RETURN @importDate;
END
GO

score 3 · Answer 3 · edited Dec 10 '12 at 16:09

3

Then how about:

declare @dt datetime='1 dec 2012'

select case when 8-@@DATEFIRST=DATEPART(dw,@dt)  
            then DATEADD(d,-2,@dt)  
        when (9-@@DATEFIRST)%7=DATEPART(dw,@dt)%7  
            then DATEADD(d,-3,@dt)  
        else DATEADD(d,-1,@dt)  
    end

edited Dec 10 '12 at 16:09

Xavi López

27,550
11
97
161

answered Dec 10 '12 at 15:47

noworries

31
2

score 1 · Answer 4 · answered Sep 21 '16 at 16:03

You can easily make this a function call, adding a second param to replace GetDate() with whatever date you wanted. It will work for any day of the week, at any date range, if you change GetDate(). It will not change the date if the day of week is the input date (GetDate())

Declare @DayOfWeek As Integer = 2   -- Monday

Select DateAdd(Day, ((DatePart(dw,GetDate()) + (7 - @DayOfWeek)) * -1) % 7, Convert(Date,GetDate()))

score 1 · Answer 5 · answered Apr 30 '18 at 14:17

1

More elegant:

select DATEADD(DAY, 
CASE when datepart (dw,Getdate()) < 3 then datepart (dw,Getdate()) * -1 + -1 ELSE -1 END,
cast(GETDATE() as date))

answered Apr 30 '18 at 14:17

Jason Lloyd

11
1

score 1 · Answer 6 · edited May 23 '17 at 12:32

The simplest solution to find the previous business day is to use a calendar table with a column called IsBusinessDay or something similar. The your query is something like this:

select max(BaseDate)
from dbo.Calendar c
where c.IsBusinessDay = 0x1 and c.BaseDate < @InputDate

The problem with using functions is that when (not if) you have to create exceptions for any reason (national holidays etc.) the code quickly becomes unmaintainable; with the table, you just UPDATE a single value. A table also makes it much easier to answer questions like "how many business days are there between dates X and Y", which are quite common in reporting tasks.

score 0 · Answer 7 · answered Oct 23 '15 at 08:48

thanks for the tips above, I had a slight variant on the query in that my user needed all values for the previous business date. For example, today is a Monday so he needs everything between last Friday at midnight through to Saturday at Midnight. I did this using a combo of the above, and "between", just if anyone is interested. I'm not a massive techie.

-- Declare a variable for the start and end dates.
declare @StartDate as datetime 
declare @EndDate as datetime 

SELECT  @StartDate = DATEADD(DAY, CASE DATENAME(WEEKDAY, GETDATE()) 
WHEN 'Sunday' THEN -2 
WHEN 'Monday' THEN -3 
    ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))
select @EndDate = @StartDate + 1 
select @StartDate , @EndDate 
-- Later on in the query use "between"
and mydate between @StartDate and @EndDate

score 0 · Answer 8 · edited Mar 29 '12 at 09:53

0

select  
  dateadd(dd, 
             case DATEPART(dw, getdate()) 
             when 1 
             then -2 
             when 2 
             then -3 
             else -1 
         end, GETDATE())

edited Mar 29 '12 at 09:53

Arion

31,011
10
70
88

answered Mar 29 '12 at 09:47

paul

21,653
1
53
54

Using `DATEPART(dw...` without knowing the `DATEFIRST` setting is [ambiguous](http://msdn.microsoft.com/en-us/library/ms174420.aspx). Don't do it. – Damien_The_Unbeliever Mar 29 '12 at 13:29

score -1 · Answer 9 · edited Jun 18 '23 at 01:46

Dale Kilian's code is slightly buggy, here is the corrected version, where this also considers holiday's stored in another table.

Please ignore my formatting error, I am not good at posting here.

ALTER FUNCTION [dbo].[fnGetPreviousWorkDay]
(
    @inputDate DATE
)
RETURNS DATE
AS
BEGIN

DECLARE @outputDate DATE =  DATEADD(day,-1,@inputDate )
--Holidays
IF EXISTS(SELECT 1 FROM HoliDayList WHERE @outputDate = HoliDayDate)
BEGIN
SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END
--Satruday
IF(DATEPART(WEEKDAY,@outputDate ) = 7)
BEGIN
    SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END
--Sunday
IF(DATEPART(WEEKDAY,@outputDate ) = 1)
BEGIN
    SET @outputDate = (SELECT dbo.fnGetPreviousWorkDay(@outputDate ))
END


RETURN @outputDate ;

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

9 Answers9

Linked

Related