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

Question

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

Whats the point of `TOP (10000000) `? – Dale K Aug 10 '22 at 06:16 — Dale K, Aug 10 '22 at 06:16

score 1 · Answer 1 · edited Aug 10 '22 at 06:04

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

score 0 · Accepted Answer · answered Aug 10 '22 at 05:46

0

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

answered Aug 10 '22 at 05:46

Sriram

433
4
20

score -1 · Answer 3 · edited Aug 10 '22 at 06:04

-1

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

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

edited Aug 10 '22 at 06:04

Dale K

25,246
15
42
71

answered Aug 10 '22 at 05:37

learning

608
5
15

it will get all the data from last 7 days. I want the data till 7am today – Sriram Aug 10 '22 at 05:38

score -2 · Answer 4 · answered Aug 10 '22 at 05:36

-2

Try dateadd

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

4 Answers4