How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates

It's tricky to use dates in the SQL server query, especially if you don't have good knowledge of how DateTime type works in the SQL server. For example, one of the frequently asked SQL queries on the interview is to "select all rows where the date is 20151007?" How would you do that? Does the following SQL Query will work correctly

select * from table where date = '20151007'

It may or may not, it entirely depends on upon data in your table. When you only provide date part of a DateTime variable, it uses '00:00:00.000' for the time part.

So if you have any row which has the same date but different time then this query will not work. For example, you have the Order table where you have two orders, one with order_date='20150107' and other with order_date='20150107:01:00:00:000', then above query will only return first order.  I'll explain how to find rows between dates in SQL Server in little more detail in this article.

Many Java or C++ programmer who uses SQL Server doesn't pay enough attention to the data type of date columns e.g. order_date, trade_date, etc and many of them don't even know that how SQL Server will compare their condition with the data in that column. 

It's not surprising because for them SQL is a secondary skill, while C++ or Java is a primary skill, but, to be honest there would be very few real-world applications where you don't have to work on SQL

Query with Comparing Dates in SQL

In order to understand how dates are compared in SQL, let's see an example where DateTime field have some time values as well.

Suppose we have the following table with a varchar course_name and datetime course_date columns:

IF OBJECT_ID( 'tempdb..#Course' ) IS NOT NULL
DROP TABLE #Course;

CREATE TABLE #Course (course_name varchar(10), course_date datetime);

INSERT INTO #Course VALUES ('Java', '2015-10-06 11:16:10.496');
INSERT INTO #Course VALUES ('MySQL', '2015-10-07 00:00:00.000');
INSERT INTO #Course VALUES ('SQL SERVER', '2015-10-07 11:26:10.193' );
INSERT INTO #Course VALUES ('PostgreSQL', '2015-10-07 12:36:10.393');
INSERT INTO #Course VALUES ('Oracle', '2015-10-08 00:00:00.000');

Now, you need to write a query to get all courses which are on '2015-10-07'? Right query, should return 3 rows containing dates starting with '2015-1-07', as shown below:

'MySQL',      '2015-10-07 00:00:00.000'
'SQL SERVER', '2015-10-07 11:26:10.193'
'PostgreSQL', '2015-10-07 12:36:10.393'

Let's see how different solution works:

If you just compare dates with = operator and only provides date, you will get the rows where time field is zero because SQL server will use '00:00:00.000" for time, as seen in the following example :

SELECT * FROM #Course WHERE course_date = '2015-10-07'
course_name course_date
MySQL 2015-10-07 00:00:00.000

You can see it only brings one row, where time is zero, it didn't match the other two rows where time is non-zero. See Querying Microsoft SQL Server book to learn more about how SQL server handles date formats and missing date and time information.

Solution 2 - Comparing dates with between clause

It seems between is the right option to compare dates without times. You can put current date and next date to cover all times where the date is same, but unfortunately that will not work. It will also grab the next date value as seen below:

SELECT * FROM #Course WHERE course_date between '2015-10-07' 
and '2015-10-08'
course_name course_date
MySQL       2015-10-07 00:00:00.000
SQL SERVER  2015-10-07 11:26:10.193
PostgreSQL  2015-10-07 12:36:10.393
Oracle      2015-10-08 00:00:00.000

You can see between also fetched 2015-10-08 00:00:00.000 values, which is not desirable. This happens because BETWEEN clause will always pull any values of next date midnight.  See Querying Microsoft SQL Server 2012 to learn more on this topic. The discussion is valid for SQL Server 2014 as well.

Always Use >= and < to compare dates

The right answer is to use the greater than (>) and less than (<) operators. This will work as expected. In order to use this option, just put the date and next date in where clause as shown below:

SELECT * FROM #Course WHERE course_date >= '2015-10-07' 
and course_date < '2015-10-08'
course_name course_date
MySQL       2015-10-07 00:00:00.000
SQL SERVER  2015-10-07 11:26:10.193
PostgreSQL  2015-10-07 12:36:10.393

You can see we got exactly three 3 rows as expected. Just remember to use < on the second predicate, this will ensure that  '2015-10-08 00:00:00.000' will not get picked up.

That's all about how to compare date columns in SQL Server. You can see that it's very easy to get the SQL query with date incorrect. Sometimes you feel your query is working fine but it failed in the real environment, Why? because of different data in both environment. 

In QA if you don't have any midnight date value then the date with between clause will work fine but if you a midnight value in the real environment it will fail.

The right way to compare date only values with a DateTime column is by using <= and > condition. This will ensure that you will get rows where date starts from midnight and ends before midnight e.g. dates starting with '00:00:00.000' and ends at "59:59:59.999".

Also worth remembering is that when you do date = '2015-10-08' and if the date is DateTime column then SQL Server will use '2015-10-08 00:00:00.000' value. So please aware of that. Btw, if you want to practice challenging SQL queries then you can also check Joe Celko's SQL Puzzles and Answers, Second Edition, one of the interesting books to improve SQL skill.

Other SQL Server articles you may like

• How to join three tables in one SQL Query? (tutorial)
• How to delete from a table using join in SQL? (tutorial)
• 5 SQL Server Management Studio tips worth knowing (tips)
• How to replace null with empty String in SQL Server? (solution)
• Difference between WHERE and HAVING clause in SQL? (answer)
• 50 SQL Server Interview questions with answers (SQL Server questions)
• How to add columns on existing table in Microsoft SQL Server? (solution)
• Difference between row_number(), rank(), and dense_rank() in SQL? (answer)
• How to increase the length of existing varchar column in SQL Server? (solution)
• How to get just date or time from GETDATE() function in SQL Server? (answer)
• Difference between SQL queries in Oracle and Microsoft SQL Server? (answer)
• How to find the length of String in MSSQL? (solution)
• SQL query to find all table names in a database? (query)

It's important for any programmer to be familiar with date and time concepts in SQL Server because they are tricky concept but at the same time very important one also. So I highly recommend you to put time aside and practice these date related queries in SQL Server Management Studio.