SQL, get any data between two days and specific time-CodePudding

I am trying to get any data that is between that time range of two days ago until yesterday. Example: Retrieve any data between 3 PM two days ago and yesterday 3 PM. This query should work on the daily basis.

I am thinking something like but just don't know where to insert the time

select * from dbo.table where system_date between getdate()-2 and getdate()-1

CodePudding user response：

You can use CAST(CAST(GETDATE() AS date) AS datetime) to get the beginning of today's date, then use DATEADD to subtract 1 or 2 days, and add 15 hours.

I strongly suggest you use >= AND < on dates, rather than BETWEEN, otherwise you get "on the interval" issues.

SELECT t.*
FROM dbo.[table] t
WHERE t.system_date >= DATEADD(hour, 15, DATEADD(day, -2, CAST(CAST(GETDATE() AS date) AS datetime)))
  AND t.system_date <  DATEADD(hour, 15, DATEADD(day, -1, CAST(CAST(GETDATE() AS date) AS datetime)));

CodePudding user response：

try this

select * 
from dbo.table 
where system_date between dateadd(day, datediff(day, 2, getdate()), '15:00:00') and dateadd(day, datediff(day, 1, getdate()), '15:00:00')

CodePudding user response：

You should use DATEADD for subtracting dates. Your query will look like this.

select *
from table
where system_date between dateadd(day, -2, getdate()) and dateadd(day, -1, getdate())