Des Hutch Des Hutch - 5 months ago 9
SQL Question

Weekending date return issue

I'm adding in a weekending date into a column based on a specified date. The weekending date should be the following Sunday unless the date is a Sunday in which case it should use that date.

Declare @DateValue DateTime = '6/12/2016' --A Sunday

select DATEADD (dd, -1 * DatePart (DW, @DateValue) + 8, @DateValue)


This query returns 2016-06-19 which is the following Sunday and I want to return 2016-06-12.

Answer

How about this

select DATEADD (dd, iif(DatePart (DW, @DateValue)=1,0,-1 * DatePart (DW, @DateValue) + 8), @DateValue)

If you don't have SQL 2012+ as suggested by bjones

select DATEADD (dd, case when DatePart (DW, @DateValue)=1 then 0 else -1 * DatePart (DW, @DateValue) + 8 end, @DateValue)