Query records between two dates and times within those dates

Question

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

Answer 1

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.

Answer 2

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'

Answer 3

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.

Answer 4

Or try:

CREATE TABLE #test(DDATE DATE,TTime VARCHAR(25))
INSERT INTO #test VALUES('2021-09-13','05:00:00'),
('2021-09-14','04:59:59'),('2021-09-13','06:00:00'),
('2021-09-14','06:00:00')

SELECT * FROM #test

;WITH cte AS(SELECT *,
CAST(CAST(DDATE AS VARCHAR(25))+' '+
CAST(TTime AS VARCHAR(25)) AS DATETIME) dt
FROM #test)

SELECT * FROM cte
WHERE dt BETWEEN '2021-09-13 05:00:00 ' AND '2021-09-14 05:00:00 '

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.