Querying a date range

All we need is an easy explanation of the problem, so here it is.

i query this SQL query:

SELECT * FROM [DB].[dbo].[Table]
WHERE [DATE] BETWEEN '01-01-2016' AND '31-03-2016'
AND ([TIME] >= '00:00:00' OR [DATE] > '01-01-2016')
AND ([TIME] <= '00:00:00' OR [DATE] < '31-03-2016');

But in SQL Studio the result to another date and time.

enter image description here

[TIME] = VARCHAR(10) (HH:MM:SS) 24h

How to fix ?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Priority 1: Fix the table. You should not be storing date and time data, in varchar columns, and quite possibly should not be separating date and time into their own separate columns. You should also avoid reserved words/keywords as columns, but I suspect you may have just dumbed down your actual table structure.

Priority 2: Stop using BETWEEN. This can cause all kinds of issues, especially if you fix the table and combine the date and time into a single column. The end of a range is different depending on the data type, but using >= beginning of the range and < beginning of the NEXT range will always work. Consider that the table has changed, you can say as follows (and I’m not sure what you’re trying to do with your second and third where clauses, but you should just be able to look at the date column):

WHERE [DATE] >= '20160101'
  AND [DATE] <  '20160401';
-- note that I don't have to look at the TIME column at all
-- also note that I use a *SAFE* format for the string literal

Priority 3: Stop comparing dates as strings. If you can’t fix the table, then your clauses need to first convert the string columns into valid date/time values, so you aren’t comparing strings. And you need to use string formats that are safe in SQL Server; dd-mm-yyyy is not one of those formats – it can break (or just return the wrong data) if you change language settings, for example.

WHERE CONVERT(datetime, [DATE], 105) >= '20160101' 
  AND CONVERT(datetime, [DATE], 105) <  '20160401';
-- 103 makes sure the stored strings are interpreted as d/m/y

Additional reading:

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply