Query records between two dates and times within those dates

Cant-We-Al-lJust- Get-Along 1 Reputation point
2021-09-13T20:31:49.007+00:00

Hello All,

I want to query records between two dates and times within those dates.

So, if I wanted to get all records from 09-13-2021 at 05:00:00 in the morning through 09-14-2021 at 04:59:59 the following morning, how would I go about it?

Thanks ADawn

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,597 questions
0 comments No comments
{count} votes

6 answers

Sort by: Most helpful
  1. Erland Sommarskog 106.3K Reputation points
    2021-09-13T20:58:03.48+00:00

    For this to be possible there must actually be a column that holds this information. But if there is, it is simple:

    SELECT *
     FROM  tbl
    WHERE datecol >= '20210913 05:00'
       AND  datecol < '20210914 05:00'
    
    0 comments No comments

  2. Cant-We-Al-lJust- Get-Along 1 Reputation point
    2021-09-13T21:00:23.137+00:00

    Hello ErlandSommarskog,

    There are two columns: Date and Time.

    thanks ADawn


  3. Cant-We-Al-lJust- Get-Along 1 Reputation point
    2021-09-13T21:23:01.42+00:00

    Can anyone answer this post?


  4. EchoLiu-MSFT 14,581 Reputation points
    2021-09-14T01:35:29.273+00:00

    Hi @Cant-We-Al-lJust- Get-Along ,

    Welcome to the microsoft TSQL Q&A forum!

    For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data.We also need to see the expected result of the sample.So that we’ll get a right direction and make some test.

    Please try:

    ;WITH cte  
     as(SELECT * FROM yourtable  
        WHERE Datecolumn BETWEEN '2021-09-13' AND '2021-09-14')  
      
    SELECT * FROM cte  
    WHERE Timecolumn BETWEEN '05:00:00' AND ' 04:59:59'  
    

    If this does not solve your problem, please provide more information.

    If you have any question, please feel free to let me know.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    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. Viorel 114.5K Reputation points
    2021-09-14T07:32:03.987+00:00

    Just in case you are still interested, and you do not want to make datetime from date and time, then check this approach too:

    select * from MyTable
    where ( D = '2021-09-13' and T >= '05:00' )
       or ( D = '2021-09-14' and T <  '05:00' )
    

    where D is the date column, T — the time column.

    0 comments No comments