3

I am trying to figure out how to convert a datepart for a week to the Monday of that week.

I am using this: 

     datepart(ww, DateCompleted) as week

to group a set of orders in our system by week. My goal is for this to say instead of 37, i want it to return the Monday of week 37, i.e.: 09-10-2012. I am able to do this by defining each week per number but i would rather not spend so much time typing out each week when there is probably a better option out there, i have just been unable to find it anywhere online other than in MySQL and other programming languages (i need T-SQL please, or anything that will work on sql server 2005), and i do not know how to work with that. Thanks for anyone that can help. Ill be checking back as often as possible to provide more info if needed.

wondergoat77
  • 1,765
  • 9
  • 32
  • 60

2 Answers2

3

You can combine DATEADD and DATEPART to get the monday of the date supplied:

DATEADD(day, (-1 * DATEPART(dw, datecompleted)) + 2, datecompleted)

Example found here

Brad Christie
  • 100,477
  • 16
  • 156
  • 200
  • 1
    @wondergoat77: No problem, but be warned: Using this method makes Sundays the week of the following Monday. (i.e. this past sunday [9/9/2012] is the week of 9/10/2012) So if you want your week to be Monday-Sunday, this needs modification. – Brad Christie Sep 13 '12 at 17:15
2

of course i found a solution right after posting this, thanks for anyone who looked and tried to help. Here is the solution i found:

SET DATEFIRST 1
declare @wk int  set @wk = 1
declare @yr int  set @yr = 2007

select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4 -
     datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) + 1

found at this link:

http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cf233caf-4ccd-4bca-add2-b5eb467f2dd4

wondergoat77
  • 1,765
  • 9
  • 32
  • 60