0

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.

6
  • 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.
    – SMor
    Aug 12, 2021 at 15:46
  • 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
    Aug 12, 2021 at 15:56
  • 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"..
    – Dark2018
    Aug 12, 2021 at 16:05
  • 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). Aug 12, 2021 at 16:11
  • i will give it a try thanks!
    – Dark2018
    Aug 12, 2021 at 16:14

1 Answer 1

6

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;
3
  • 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
    – Dark2018
    Aug 12, 2021 at 15:17
  • 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. Aug 12, 2021 at 16:09
  • @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.
    – Dan Guzman
    Aug 12, 2021 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.