'How do I find last weeks dates Monday to Friday using SQL Server?

I'd like to find last weeks dates

SELECT *
FROM Table
Where [Date] Between '04-Jan-2016' AND '15-Jan-2016'

Every time I run my SQL Query it needs to display last week.

sqlsql-server


Solution 1:[1]

---To get the first day of the previous week in SQL Server, use the following code:

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0)

--To get the last day of the previous week:

SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4)

Solution 2:[2]

Hopefully following is what you want,

--START OF LAST WEEK    
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0)

--END OF LAST WEEK    
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 4)

DECLARE @input varchar(10)
--SET @input = '01/10/2016' 
SET @input = GETDATE()

--START OF LAST WEEK    
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
CASE DATEPART(dw,@input)
WHEN 1 THEN DATEADD(d,-1,@input)
ELSE @input
END
), 0)

--END OF LAST WEEK     
SELECT DATEADD(wk, DATEDIFF(wk, 6, 
    CASE DATEPART(dw,@input)
    WHEN 1 THEN DATEADD(d,-1,@input)
    ELSE @input
    END
), 4)

Solution 3:[3]

SELECT *
FROM Table
Where [Date] Between DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 
AND DATEADD(wk,DATEDIFF(wk,7,GETDATE()),4)

Solution 4:[4]

I needed the range from the previous Monday at 00:00:00 to the previous Sunday at 23:59:59. This is how you can get that:

SET @THIS_MONDAY = SUBDATE(DATE(NOW()), WEEKDAY(NOW()));

SELECT CONCAT(SUBDATE(@THIS_MONDAY, 7), ' 00:00:00') AS last_week_monday,
       CONCAT(SUBDATE(@THIS_MONDAY, 1), ' 23:59:59') AS last_week_sunday;

The same without setting a variable:

WITH this_week AS (SELECT SUBDATE(DATE(NOW()), WEEKDAY(NOW())) as monday)
SELECT CONCAT(SUBDATE(monday, 7), ' 00:00:00') AS last_week_monday,
       CONCAT(SUBDATE(monday, 1), ' 23:59:59') AS last_week_sunday
FROM this_week;

Solution 5:[5]

SELECT DATEADD(wk, -1, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Sunday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 2-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Monday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 3-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Tuesday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 4-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Wednesday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 5-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Thursday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 6-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Friday'
union all                                                                                        
SELECT DATEADD(wk, -1, DATEADD(DAY, 7-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))) ,'Last_Saturday'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 PriceCheaperton
Solution 2 pedram
Solution 3 Sahi
Solution 4
Solution 5 Grodz

Related Questions