CODEWITHSUNDEEP
sqlt-sqlsql-server-2005week-numberdatepart
wondergoat77
wondergoat77

Reputation: 1815

convert week datepart number to date within week (week of...)

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.

Upvotes: 3

Views: 10191

Answers (2)

Brad Christie
Brad Christie

Reputation: 101604

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

Upvotes: 3

wondergoat77
wondergoat77

Reputation: 1815

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

Upvotes: 2

Related Questions