CODEWITHSUNDEEP
c#.netsql-servert-sqlado.net
TLD
TLD

Reputation: 8135

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.

Upvotes: 0

Views: 504

Answers (3)

t-clausen.dk
t-clausen.dk

Reputation: 44316

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

or

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

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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

Upvotes: 1

Vishal Gajjar
Vishal Gajjar

Reputation: 1019

There is no built-in function for this,

But you can use:

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

Upvotes: 1

Related Questions