Select last 7 days record from current Date in SQL Server

Last Reply 11 months ago By pandeyism

Posted 11 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.


You are viewing reply posted by: pandeyism 11 months ago.
Posted 11 months ago

Hi iammann,

Refer below sample query.


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

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


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