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

sql-server dateadd
PushCode picture PushCode · Mar 16, 2010 · Viewed 13k times · Source

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!

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

Answer

Raj More picture Raj More · Mar 16, 2010

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

Related questions

How to add days to the current date?

I am trying to add days to the current date and it's working fine but when I add 360 days to the current date it gives me wrong value. eg: Current Date is 11/04/2014 And I am adding 360 Days to it, it …

Read More
Subtract minute from DateTime in SQL Server 2005

Suppose I have a datetime field whose value is 2000-01-01 08:30:00 and a duration field whose value is say 00:15 (meaning 15 minutes) If I subtract these two, I should get 2000-01-01 08:15:00 Also if I want to subtract 1:15 (means 1 hour 15 minutes), …

Read More
How to add minutes to the time part of datetime

How to add minutes(INT) to the time part of datetime ? For example : If i have datetime variable like this : @shift_start_time = 2015-11-01 08:00:00.000 @increase = 30 How to get the result : 2015-11-01 08:30:00.000

Read More