Get week ending date based on work date and week ending day number

• Question

• Given a datetime and an int from a settings table which indicates what day of the week the pay period ends on, what would be the best way to get the datetime value of the week end date following the given work date?  Example:

@workDate = '2012-10-02'

@weekEndDay = 1

Desired result based on these two inputs would be Sunday, 10/7.

Tuesday, October 2, 2012 7:09 PM

• A calendar table is always the best solution for this kind of stuff...

But failing that the following should work for you...

```DECLARE
@WorkDate DATETIME = '20121002',
@WeekEndDay TINYINT = 1

SELECT
CASE
WHEN DATEPART(dw,@WorkDate) = @WeekEndDay
THEN @WorkDate
ELSE DATEADD(dd, (7 + @WeekEndDay) - DATEPART(dw,@WorkDate), @WorkDate)
END AS LastDayOfTheWeek```

Jason Long

• Edited by Tuesday, October 2, 2012 7:31 PM
• Proposed as answer by Tuesday, October 2, 2012 7:40 PM
• Marked as answer by Wednesday, October 3, 2012 4:07 PM
Tuesday, October 2, 2012 7:30 PM
• Another approach using set-based logic, quite easy to understand and almost impossible to forget:

```DECLARE @WorkDate DATETIME = '20121002',
@WeekEndDay int = 1;

select @WorkDate+n.d
from (values(1),(2),(3),(4),(5),(6),(7)) n(d)
where @WeekEndDay = DatePart(dw,@WorkDate+n.d);```

• Proposed as answer by Wednesday, October 3, 2012 3:10 PM
• Marked as answer by Wednesday, October 3, 2012 4:07 PM
Wednesday, October 3, 2012 10:03 AM

All replies

• A calendar table is always the best solution for this kind of stuff...

But failing that the following should work for you...

```DECLARE
@WorkDate DATETIME = '20121002',
@WeekEndDay TINYINT = 1

SELECT
CASE
WHEN DATEPART(dw,@WorkDate) = @WeekEndDay
THEN @WorkDate
ELSE DATEADD(dd, (7 + @WeekEndDay) - DATEPART(dw,@WorkDate), @WorkDate)
END AS LastDayOfTheWeek```

Jason Long

• Edited by Tuesday, October 2, 2012 7:31 PM
• Proposed as answer by Tuesday, October 2, 2012 7:40 PM
• Marked as answer by Wednesday, October 3, 2012 4:07 PM
Tuesday, October 2, 2012 7:30 PM
• Another approach using set-based logic, quite easy to understand and almost impossible to forget:

```DECLARE @WorkDate DATETIME = '20121002',
@WeekEndDay int = 1;

select @WorkDate+n.d
from (values(1),(2),(3),(4),(5),(6),(7)) n(d)
where @WeekEndDay = DatePart(dw,@WorkDate+n.d);```

• Proposed as answer by Wednesday, October 3, 2012 3:10 PM
• Marked as answer by Wednesday, October 3, 2012 4:07 PM
Wednesday, October 3, 2012 10:03 AM