How to get week ending date of a given date returned from a DateAdd expression

Question

Not sure how to take my current expression and get the week ending date of the week.

My current expression returns the date, one week from today.

DateAdd(DateInterval.WeekOfYear, 1, today())

What I want to do instead is return the end date (Saturday) of that week instead.

What do I need to add to this expression to get the desired result?

Thanks!

Answer 1

Try this

Declare @DateValue DateTime = '3/1/2010'

select DATEADD (D, -1 * DatePart (DW, @DateValue) + 7, @DateValue)

I basically calculated what day of the week then incoming date was, and then soft-computed the beginning of the week with the -1 multiplier, and traversed forward to the Saturday by adding 7 to it.

Of course, this will only work if you SQL Server is set to the week starting on Sunday

For SQL Server reporting services, I think this should work for Sat of current week

DATEADD (DateInterval.Day, -1 * DatePart (DateInterval.DayOfWeek, Today()) + 7, Today())

For Sat of next week

DATEADD (DateInterval.Day, -1 * DatePart (DateInterval.DayOfWeek, Today()) + 14, Today())

Answer 2

Okay, looks like I've got it but it's ugly. Can anyone help me streamline this?

Here's what I have, based on turning Raj's example into an expression:

="One Week Projected Backlog w/e "& DateAdd(DateInterval.Day, 
  -1 * DatePart(DateInterval.WeekDay, 
  (DateAdd(DateInterval.WeekOfYear, 1, today()))) + 7, 
  (DateAdd(DateInterval.WeekOfYear, 1, today())))