0

I calculate a shipping date by subtracting the lead time from the due date. If the calculated shipping date lands on a Saturday or Sunday, I would like the calculated date to change to the preceding Friday.

When I use this WHERE statement (found here), the records that fall on Saturday or Sunday are excluded from the results. 

SELECT
  due_date, 
  due_date - CAST(lead_time AS NUMERIC(3,0)) AS [Latest Ship Date]
FROM order_line_table
WHERE (((DATEPART(dw, due_date - CAST(lead_time AS NUMERIC(3,0)))
  + @@DATEFIRST) % 7) NOT IN (0,1)

Is there a way to default to Friday when Saturday or Sunday is calculated? This thread suggests a CASE in a join, but this gave me conversion error

Msg 402, Level 16, State 1, Line 27.

The server is configured so that Sunday is day 1 and Saturday is day 7.

Community
  • 1
  • 1
dmatthew
  • 15
  • 5

1 Answers1

1

Try this:

select 
  due_date,
  case DATEPART(dw, [Latest Ship Date]) 
    when 1 then DATEADD(DAY, -2, [Latest Ship Date])
    when 7 then DATEADD(DAY, -1, [Latest Ship Date])
    else [Latest Ship Date]
  end as [Latest Ship Date]
from (SELECT
      due_date, 
      due_date - CAST(lead_time AS NUMERIC(3,0)) AS [Latest Ship Date]
      FROM order_line_table) x
Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • The original code is using `+ @@DATEFIRST with modulo 7` and is therfore culture independant. This solution isn't... – Shnugo Aug 01 '16 at 22:48
  • Thank you, that worked. @Shnugo, thank you for the additional information about modulo 7. I have noted this and notified the users. – dmatthew Aug 02 '16 at 16:52