Find a week date range by current date in sql?

Question

I want to find a record this week, just announce the current date. Example:

select datename(dw,getdate()) -- Example today is Friday "27-04-2012" 

so how can i get a date range

  start on monday "23-04-2012" or sunday "22-04-2012" As @dateStart to end on sunday "29-04-2012" or saturday "28-04-2012" As @dateEnd 

then I can select a request

  select * from table where date> =@dateStart AND date< =@dateEnd 

Answer 1

Here are some useful commands for getting each day of the week.

 SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -2) SatOfPreviousWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) SunOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 0) MondayOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 1) TuesOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 2) WedOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 3) ThursOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 4) FriOfCurrentWeek, DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) SatOfCurrentWeek 

you must use them in your query to get a date range:

 SELECT * FROM yourTable WHERE yourDate >= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), -1) -- Sunday AND yourDate <= DATEADD(wk, DATEDIFF(wk,0,GETDATE()), 5) -- Saturday 

Answer 2

So, you only want recordings from this week?

 SELECT t.* FROM table t WHERE t.date >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) / 7 * 7, 0) AND t.date <= DATEADD(DAY, DATEDIFF(DAY, -1, GETDATE()), 0) 

Answer 3

Queries suggested by others may work, but you have not determined what “week” is for you. Depending on the country / region and business reasons, the week may be Sun-Sat, Mon-Sun, Mon-Fri, etc.

Therefore, the best solution for finding the first and last days of the week is probably the calendar table, where you pre-fill the first and last days of the week for each day that you need. You can then use a simple query to get the correct start and end dates to use in your request:

 select @StartDate = FirstDayOfWeek, @EndDate = LastDayOfWeek from dbo.Calendar where BaseDate = @SomeDate 

This solution also has the advantage that you can easily work with different week definitions by adding new columns to your calendar table.