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.

• SMor: If you really ran the query you posted with the data you posted and your column is actually datetime (and not string or something else), then what you claim is impossible. That query will include the rows from Aug 29 and Aug 30 (but not Sept 1) as demonstrated here.
• Dark2018: yes you are right. Maybe i should've add DATEADD(day, 1, @EndDate) to get to Sept the 1st. Anyway apart for this, if we assume that I wanted to get up to 30 august..that query does not do what I want
• Dark2018: strange thing is I run your query exactly as it is into dbfiddle.uk and it works. Then I copied and paste it into sql server and is not working..it says "the conversion of a type varchar in datetime generated a value out of the interval"..
• Tibor Karaszi: That fiddle also suffers from non-language independent language formats. Try the suggestion I have in the other comment I made (yyyymmdd instead of yyyy-mm-dd).
• Charlieface: Dates don't have a format, they are stored in binary. Always, always store them in date or datetime columns, not varchar

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;

• Dark2018: thanks for answering. The first option does not provide any results in sql as well as the second in which by the way there is an error as I guess you forgot the term datetime after @EndDate. Both don't work
• Tibor Karaszi: You probably have logged in with a language that don't interpret the order of the month vs date as expected. They the format such as '20210829' and see if that makes a difference.
• Dan Guzman: @Dark2018, my bad, I corrected the second code example. But the code should otherwise work. I used the same dateformat as in your original question but, as Tibor mentioned, the parsed value of the literal depends to the dateformat session setting. Both queries will work with an ISO 8601 datetime literal.