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

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.

开发者_如何学CDateAdd(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!

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())))

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())

How about?

select dateadd(ww,-2,'1-27-2014')+4