Get last 7 days data from today 7am to last 7 days 7am-CodePudding

I have a query that currently can view the timestamp between yesterday 7am and today 7am data. How do I extract the timestamp of last 7 day timestamp. For example, if today is 10th August 2022, I would like to see the data of 3rd August 7am to 10th August 7am data. This is my current SQL query:

  select TOP (10000000) id, PartNum, TimeStamp,Station
  from test_module
  where TimeStamp >= '2022-07-16 07:00:00' 
  and Timestamp <= '2022-07-17 07:00:00'
  order by TimeStamp asc

CodePudding user response：

datetimes can participate in addition. Thus, you can construct the day parts(remove the time first), and then add 7 hours as a time:

declare @start datetime = dateadd(day,-6,convert(datetime,(convert(date,getdate())))   convert(datetime,convert(time,'07:00:00'))
declare @end   datetime = dateadd(day, 0,convert(datetime,(convert(date,getdate())))   convert(datetime,convert(time,'07:00:00'))

Be careful tossing the term "timestamp" around in SQL Server, it could lead to...miscommunications...

CodePudding user response：

 Timestamp >= dateadd(hour, 7, dateadd(day, -7, convert(datetime, 
 convert(date, getdate()))))
 and Timestamp <= dateadd(hour, 7, convert(datetime, convert(date, 
 getdate())))

CodePudding user response：

Can use getdate() function and for last 7 days, -7 For instance,

where DateCol between DateAdd(DD,-7,GETDATE() ) and GETDATE()

CodePudding user response：

Try dateadd