CODEWITHSUNDEEP
sqlsql-serversql-timestamp
Sriram
Sriram

Reputation: 453

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

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

Upvotes: 0

Views: 356

Answers (4)

learning
learning

Reputation: 610

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

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

Upvotes: -1

George Menoutis
George Menoutis

Reputation: 7260

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

Upvotes: 1

Sriram
Sriram

Reputation: 453

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

Upvotes: 0

meridbt
meridbt

Reputation: 405

Try dateadd

See also thread here

Upvotes: -2

Related Questions