CODEWITHSUNDEEP
sqlsql-server
PriceCheaperton
PriceCheaperton

Reputation: 5349

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.

Upvotes: 13

Views: 35937

Answers (5)

Grodz
Grodz

Reputation: 1

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'

Upvotes: 0

Leandro
Leandro

Reputation: 1522

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;

Upvotes: 1

Sahi
Sahi

Reputation: 1484

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

Upvotes: 4

Willie Cheng
Willie Cheng

Reputation: 8253

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)

Upvotes: 6

PriceCheaperton
PriceCheaperton

Reputation: 5349

---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)

Upvotes: 7

Related Questions