question

ShurondaHawkins-0941 avatar image
0 Votes"
ShurondaHawkins-0941 asked BertZhoumsft-7490 commented

SQL Query for current and previous date based on time range

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'

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,@ShurondaHawkins-0941
Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)

Best regards,
Bert Zhou

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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;

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

JingyangLi avatar image
0 Votes"
JingyangLi answered
 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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BertZhoumsft-7490 avatar image
0 Votes"
BertZhoumsft-7490 answered BertZhoumsft-7490 edited

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.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.