CODEWITHSUNDEEP
sqlsql-server-2012
Bulbul
Bulbul

Reputation: 157

SQL Date Issue for Weekly Report Data

I need to run a weekly report based on the arrival date. How can I set the arrival date in the where clause so that I can get the result only for each week. The hard part is I DO NOT want to modify the dates each week. I need the permanent where clause for the date. I have to provide a list of customers who arrived every week and I just want to run the same script without changing the week dates.

Please assist. Thanks.

Upvotes: 0

Views: 104

Answers (2)

Gabriel
Gabriel

Reputation: 3604

SELECT * FROM TABLE WHERE 
(ARRIVAL_DATE>DATEFROMPARTS(YEAR(GETDATE()-7), MONTH(GETDATE()-7), DAY(GETDATE()-7)))//7 days before starting at midnight
AND 
(ARRIVAL_DATE<DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), DAY(GETDATE()))) //NOW in the YYYY, MM,DD format

Upvotes: 1

LeeG
LeeG

Reputation: 728

This will get everything that happened in the current calendar week (Mon-Sun)

SELECT * FROM Table1
WHERE ArrivalDate BETWEEN
CAST(DATEADD(dd,(((DATEPART(dw,getdate())+@@DATEFIRST) % 7)+5) % 7 ,getdate()) as date) AND
CAST(DATEADD(dd,6+((((DATEPART(dw,getdate())+@@DATEFIRST) % 7)+5) % 7 ),getdate()) as date)

Edit - Removed extra E in BETWEEN

Upvotes: 0

Related Questions