Get date of start day (ex Monday) of a week when having a current date of a random day (ex Friday) in that week

For example, my starting day of the week is Monday (2/1/2011), when user input is (4/1/2011) Wednesday开发者_如何学编程, is there any built-in functions that can help me get the starting date of the current week?

Thank you.

---

Assuming that SQL server's DATEFIRST setting fits in with your idea of how weeks are aligned (this probably only matters for Saturdays, Sundays, etc), then a DATEADD/DATEDIFF pair from an arbitrary Monday should give the result you want:

select DATEADD(week,DATEDIFF(week,'20110103',CURRENT_TIMESTAMP),'20110103')

Where CURRENT_TIMESTAMP obviously picks todays date. If you were selecting from a table (Tab), with a column called RandomDate, then you might do:

select DATEADD(week,DATEDIFF(week,'20110103',RandomDate),'20110103') as MondayDate
from Tab

But I'm confused by your example, since neither the 2nd of January nor the 1st of February (usual interpretation possibilities for '2/1/2011') is a Monday. The random Monday I selected was '20110103' - so if, instead, you needed to find the friday for a particular date, you'd use '20110107' in both places where '20110103' appears

---

There is no built-in function for this,

But you can use:

SELECT      DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)

---

select getdate() - CAST(getdate()-.5 as int)%7

or

select cast(getdate() - CAST(getdate()-.5 as int)%7 as date)