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)