title: calculate how to get GETDATE() -4 using customer function in SQL
body: <p>I have a function in my database that is able to spit out a business date like so...</p>
  
  <p>Let's say today is 3/23/2020 - if I run the function like this:</p>
  
  <pre><code>select [dbo].[ufGetBusinessDay] (DATEADD(day, -1,GETDATE()) ,'PREVIOUS') 
  </code></pre>
  
  <p>it will show '03/20/2020' (previous business day. It knows to skip the weekend)</p>
  
  <p>if I do something like </p>
  
  <pre><code>select [dbo].[ufGetBusinessDay] (DATEADD(day, +1,GETDATE()) ,'NEXT') 
  </code></pre>
  
  <p>it will show '03/24/2020' (next business day)</p>
  
  <p>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.</p>
  
  <p>My goal is to create a filter in a proc that lets me go back 4 days back like so....</p>
  
  <pre><code>Select * from tbl1 where DateField &gt;=DATEADD(dd,-4,GETDATE()) and DateField &lt; GETDATE()
  </code></pre>
  
  <p>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</p>
  
  <pre><code>DECLARE @daysago AS DATETIME
  
  SELECT @daysago= [dbo].[ufGetBusinessDay] (DATEADD(day, -1,GETDATE()),'PREVIOUS') 
  
  SELECT DATEADD(DAY,-4,@daysago)
  </code></pre>
  
  <p>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 <code>DATEADD(DAY,-4,TUESDAY)</code> 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.</p>
  
  <p>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.</p>