Find the Date range of week by current date in sql?

sql, sql-server-2005, sql-server-2008

Solution

Here are some helpful commands to get 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 would then use these in your query to get the 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

Problem

I want to find record by this week just declare current date. Example: ``` select datename(dw,getdate()) -- Example today is Friday "27-04-2012" ``` so how can i get 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 query by ``` select * from table where date>=@dateStart AND date<=@dateEnd ```

Original source