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

sql tsql sql-server-2005 week-number datepart
wondergoat77 picture wondergoat77 · Sep 13, 2012 · Viewed 10k times · Source

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.

Answer

Brad Christie picture Brad Christie · Sep 13, 2012

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

Related questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More
How can I do an UPDATE statement with JOIN in SQL Server?

I need to update this table in SQL Server with data from its 'parent' table, see below: Table: sale id (int) udid (int) assid (int) Table: ud id (int) assid (int) sale.assid contains the correct value to update ud.…

Read More