SQL Query for current and previous date based on time range

ShurondaHawkins-0941 1 Reputation point
2022-04-26T15:27:53.683+00:00

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'

Developer technologies Transact-SQL
SQL Server Other
{count} votes

4 answers

Sort by: Most helpful
  1. Naomi Nosonovsky 8,431 Reputation points
    2022-04-26T15:35:08.427+00:00

    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;

    0 comments No comments

  2. Jingyang Li 5,896 Reputation points Volunteer Moderator
    2022-04-27T02:20:54.797+00:00
    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
    
    0 comments No comments

  3. Bert Zhou-msft 3,436 Reputation points
    2022-04-27T02:34:06.31+00:00

    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.

    0 comments No comments

  4. Shayshank Rathore 1 Reputation point
    2022-11-30T13:26:34.507+00:00

    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

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.