Select last 7 days record from current Date in SQL Server

Last Reply 3 months ago By pandeyism

Posted 3 months ago

Suppose today is Friday and i want to get data on every friday.From last Friday to Thursday of every Week. Example: 19-07-2019(Friday) So i want data from 12-07-2019(Friday)-to -18-07-2019(Thurday)

Same process will run every week

ClientID    Name    Country    Amount    Last_Modified_Date 

these are the column on which we want to get data.

 

Posted 3 months ago

Hi iammann,

Refer below sample query.

SQL

DECLARE @tblWeekData AS TABLE(ClientId INT IDENTITY, Name VARCHAR(20), Country VARCHAR(20), Amount INT, Last_Modified_Date DATETIME)
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-05')
INSERT INTO @tblWeekData VALUES('John Hammond','United States',100,'2019-07-06')
INSERT INTO @tblWeekData VALUES('Suzanne Mathews','France',100,'2019-07-07')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-08')
INSERT INTO @tblWeekData VALUES('Suzanne Mathews','France',100,'2019-07-09')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-10')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-11')
INSERT INTO @tblWeekData VALUES('Suzanne Mathews','France',100,'2019-07-12')
INSERT INTO @tblWeekData VALUES('John Hammond','United States',100,'2019-07-13')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-14')
INSERT INTO @tblWeekData VALUES('John Hammond','United States',100,'2019-07-15')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-16')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-17')
INSERT INTO @tblWeekData VALUES('Mudassar Khan','India',100,'2019-07-18')

DECLARE @CurrentDate DATETIME
SET @CurrentDate = '2019/07/12'
DECLARE @From DATETIME
SET @From = @CurrentDate - 7
DECLARE @To DATETIME
SET @To = @CurrentDate - 1
DECLARE @WeekDays VARCHAR(20)
SET @WeekDays = (SELECT DATENAME(dw,@CurrentDate))
IF (@WeekDays = 'Friday')
	SELECT * FROM @tblWeekData WHERE Last_Modified_Date BETWEEN @From AND @To
ELSE
PRINT 'Nothing'

Output

ClientId Name Country Amount Last_Modified_Date
1 Mudassar Khan India 100 7/5/2019 0:00
2 John Hammond United States 100 7/6/2019 0:00
3 Suzanne Mathews France 100 7/7/2019 0:00
4 Mudassar Khan India 100 7/8/2019 0:00
5 Suzanne Mathews France 100 7/9/2019 0:00
6 Mudassar Khan India 100 7/10/2019 0:00
7 Mudassar Khan India 100 7/11/2019 0:00