3

I need to write a query that gives me 3 working days prior to a given date without using functions. I don't have a huge knowledge of SQL and wanted to know if this simple query will suffice.

DECLARE @date DATETIME
SET @date = '7 september 2013' 

SELECT DATEPART(dw, @date), 
CASE WHEN DATEPART(dw, @date) = 1 THEN @date - 4
WHEN DATEPART(dw, @date) =2 THEN @date - 5
WHEN DATEPART(dw, @date) =3 THEN @date - 5
WHEN DATEPART(dw, @date) =4 THEN @date - 5
WHEN DATEPART(dw, @date) =5 THEN @date - 3
WHEN DATEPART(dw, @date) =6 THEN @date - 3
WHEN DATEPART(dw, @date) =7 THEN @date - 3
 END AS '3 Business days prior'
Taryn
  • 242,637
  • 56
  • 362
  • 405
Nas
  • 51
  • 1
  • 3
  • 1
    poster wants working days rather than calendar – Mitch Wheat Sep 16 '13 at 08:17
  • 4
    What about holidays? Use a calendar table: http://stackoverflow.com/questions/14749877/t-sql-get-number-of-working-days-between-2-dates – Mitch Wheat Sep 16 '13 at 08:18
  • 5
    You could use the [IN](http://technet.microsoft.com/en-us/library/ms177682.aspx) operator to simplify your query somewhat. (`when datepart(dw, @date) in (5,6,7) then @date - 3`) – Hans Kesting Sep 16 '13 at 08:28
  • I agree with Hans. My solution is to create a calendar table with working days that serves as a lookup table identifying what is & is not a working day. – BI Dude Jun 12 '14 at 17:38

1 Answers1

0

If you only need 3 days before, excluding saturdays and sundays and holidays is not an issue, then you can use the workday(W):

DECLARE @date DATETIME SET @date = '7 september 2013'

select dateadd(w, -3, @date)

Rui
  • 1