0

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Xitrum
  • 7,765
  • 26
  • 90
  • 126
  • Possible dublicate http://stackoverflow.com/questions/38039/how-can-i-get-the-datetime-for-the-start-of-the-week – Claus Jørgensen Aug 16 '11 at 08:22
  • Click This Link For details.I think it will be solve your problem. [Click Here][1] [1]: http://stackoverflow.com/questions/10684399/get-the-first-day-and-last-day-of-the-week-of-a-given-date/10685083#10685083 – yeasir007 May 22 '12 at 03:43

3 Answers3

1

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

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • sorry i just picked random date – Xitrum Aug 16 '11 at 08:26
  • sorry i don't really understand you solution, today is 08/16/2011 so i tried select DATEADD(week,DATEDIFF(week,'20110816',CURRENT_TIMESTAMP),'20110816') and it returned me 2011-08-16 00:00:00.000 :( – Xitrum Aug 16 '11 at 08:33
  • Ah, sorry. The bit where you're meant to plug in your own date is where I currently have `CURRENT_TIMESTAMP`. the `'20110103'` bit always stays the same. – Damien_The_Unbeliever Aug 16 '11 at 08:35
  • ah I know I understand your solution, but as I mention, I only have the current date and I know that Monday will be the starting day of the week, so date of monday is the thing i want to get :( – Xitrum Aug 16 '11 at 08:38
  • @user552279 - the correct statement to find the Monday from today's date would be: `DATEADD(week,DATEDIFF,week,'20110103','20110816'),'20110103')` - like I said, the `'20110103'` bits always stay the same. – Damien_The_Unbeliever Aug 16 '11 at 08:40
  • You need `CURRENT_TIMESTAMP - 1` for this to work on Sundays and `DATEFIRST` does not matter. – Mikael Eriksson Aug 16 '11 at 08:40
1

There is no built-in function for this,

But you can use:

SELECT      DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0)
Vishal Gajjar
  • 1,009
  • 9
  • 20
1
select getdate() - CAST(getdate()-.5 as int)%7

or

select cast(getdate() - CAST(getdate()-.5 as int)%7 as date)
t-clausen.dk
  • 43,517
  • 12
  • 59
  • 92