sql-server - calculate how to get GETDATE() -4 using customer function in SQL

Question

I have a function in my database that is able to spit out a business date like so...

Let's say today is 3/23/2020 - if I run the function like this:

select [dbo].[ufGetBusinessDay] (DATEADD(day, -1,GETDATE()) ,'PREVIOUS') 

it will show '03/20/2020' (previous business day. It knows to skip the weekend)

if I do something like

select [dbo].[ufGetBusinessDay] (DATEADD(day, +1,GETDATE()) ,'NEXT') 

it will show '03/24/2020' (next business day)

Pretty straight forward. This is the only functionality it has. I don't know if it's necessary to share the code as this has some custom filtering not pertaining to what I am doing. For my purpose it can only go back to a previous business day, and to next business day.

My goal is to create a filter in a proc that lets me go back 4 days back like so....

Select * from tbl1 where DateField >=DATEADD(dd,-4,GETDATE()) and DateField < GETDATE()

the issue with the function is that, it only goes a day backward, so while this seems to work in this scenario, because our last business date is FRIDAY - 4 days

DECLARE @daysago AS DATETIME

SELECT @daysago= [dbo].[ufGetBusinessDay] (DATEADD(day, -1,GETDATE()),'PREVIOUS') 

SELECT DATEADD(DAY,-4,@daysago)

However in a case of a scanario where GETDATE() happens to be a Wednesday, and I run the function which will return Tuesday, and if we do DATEADD(DAY,-4,TUESDAY) it will return SUNDAY. THe goal here would be to use this function to exclude the weekend and actually go back to previous week's THursday.

EDIT: I'm not even sure if this can be done inside a stored proc utilizing this function. I think it might be actually necessary to create a seperate proc or function to do this type of thing where we check DATENAME(WEEKDAY,DATE) to determine if it's a weekend and then utilize the function I have to go back to prev business day (excluding weekend) while keeping a count how many days back we have done already.

标签： sql-servertsqldate-arithmetic

Answer 1

您应该能够将多个函数调用嵌套在一起，以返回您想要的工作日数。

select 
[dbo].[ufGetBusinessDay] (DATEADD(day, -1, 
    [dbo].[ufGetBusinessDay] (DATEADD(day, -1, 
        [dbo].[ufGetBusinessDay] (DATEADD(day, -1, 
            [dbo].[ufGetBusinessDay] (DATEADD(day, -1, GETDATE()), 'PREVIOUS'), 
        'PREVIOUS'), 
    'PREVIOUS'), 
'PREVIOUS')

您还可以使用公用表表达式 (CTE) 以递归方式调用您的函数所需的次数，然后只选择一个像这样的值。

DECLARE @XDaysAgo DATE;
DECLARE @NumberBusinessDays INT;
SET @NumberBusinessDays = -1;

WITH cte AS
(
    SELECT 0 AS DaysBack, [dbo].[ufGetBusinessDay] (DATEADD(day, -1, GETDATE(), 'PREVIOUS')) AS MyDate
    UNION ALL
    SELECT
          cte.DaysBack - 1
        , [dbo].[ufGetBusinessDay] (DATEADD(day, -1, cte.MyDate, 'PREVIOUS'))
    FROM  cte
    WHERE cte.DaysBack > @NumberBusinessDays
)
SELECT @XDaysAgo = cte.MyDate 
FROM cte WHERE cte.DaysBack = @NumberBusinessDays;