Given a table with date_time column, how to run a query using only the date

Question

I have a DB with a table which has a column datetime Date_Time. The format of this is for example: 2021-09-01 13:15:16. I would like to set a query that is indipendent of the time but just consider the date. For example, given such table:

Date_Time
2021-09-01 13:15:16
2021-09-01 10:25:12
2021-08-30 03:05:16
2021-08-29 13:15:16
...

I would like to run a query like:

SELECT Date_Time 
FROM dbo.Twitter 
WHERE Date_Time BETWEEN '2021-08-29' AND '2021-09-01' 
ORDER BY Date_Time DESC;

such query gives me nothing of course since in the BETWEEN there is no hour specified.

The desired result must be what you see above, thus all the rows from '2021-08-29 00:00:00' and '2021-09-01 23:59:59' I tried to impose myself the hours 00:00:00 and 23:59:59 in the BETWEEN command but since there are no record with that time it returns nothing.

The query should return all the result with that date whatever the hour.

Answer 1

A good practice with date range queries is to specify and inclusive start date and exclusive end date like below. This will effectively ignore the time component when the range values are exact dates (midnight).

SELECT Date_Time 
FROM dbo.Twitter 
WHERE Date_Time >= '2021-08-29' AND Date_Time < '2021-09-02' 
ORDER BY Date_Time DESC;

You can alternatively add a day to the end date value so that the date range values may be specified more naturally like the BETWEEN values in your question, including the rows on the end date:

DECLARE
    @StartDate datetime = '2021-08-29',
    @EndDate datetime = '2021-09-01';
SELECT Date_Time 
FROM dbo.Twitter 
WHERE Date_Time >= @StartDate AND Date_Time < DATEADD(day, 1, @EndDate)
ORDER BY Date_Time DESC;