SQL Query for current and previous date based on time range

Question

Hello, I am needing assistance with a sql query that pulls current date and previous date data from 7 a.m. of the previous date to 7 a.m. of the current date. I understand how to hard code the information, but am needing to set this to run every day. The hard code of the date works, but doesn't change each day.

TestTable__1."CV_CA_Confirmed_Date_Time_Local" BETWEEN '2022-04-25 07:00:00' AND '2022-04-26 07:00:00'

Answer 1

Try
declare @StartTime datetime, @EndTime datetime;

set @EndTime = CAST(CAST(CURRENT_TIMESTAMP AS DATE) AS DATETIME) + CAST(CAST('07:00:00' AS TIME) AS DATETIME);
set @StartTime = dateadd(day, -1, @EndTime);

TestTable__1."CV_CA_Confirmed_Date_Time_Local" BETWEEN @StartTime and @EndTime;

Answer 2

declare @StartTime datetime, @EndTime datetime;

set @StartTime = DATETIMEFROMPARTS(Year(getdate()),Month(getdate()),day(getdate()),7,0,0,0)
set @EndTime  = Dateadd(day, 1, @StartTime);

select  @StartTime, @EndTime

WHERE TestTable__1."CV_CA_Confirmed_Date_Time_Local">=@StartTime and TestTable__1."CV_CA_Confirmed_Date_Time_Local"<@EndTime

Answer 3

Hi，@ShurondaHawkins-0941

Welcome to Microsoft T-SQL Q&A Forum!

I think this might help you , Please check this:

SELECT *  
  FROM YourTable  
  WHERE [dateColumn] >DATEADD(day,1,'4/25/2022')  AND [dateColumn] <= DATEADD(day,1,'4/26/2022')   
    AND DATEPART(hh,[dateColumn]) >= 7 AND DATEPART(hh,[dateColumn]) <= 19  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

Hi @ShurondaHawkins-0941 ,

please try to use below script

select * from table where column_name <= dateadd(hh,7,cast(cast(GETDATE() as date) as datetime))
AND column_name >= dateadd(hh,7,cast(cast(GETDATE()-1 as date) as datetime))

Today i got this issue and i have used above to resolve it.

Thanks,
Shayshank Rathore