It's tricky to use dates in SQL server query, especially if you don't have good knowledge of how DateTime type works in 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 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 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 secondary skill, while C++ or Java is primary skill, but, to be honest there would be very few real world application where you don't have to work on SQL.

So, if you like to improve your knowledge of SQL fundamentals, particularly in SQL Server, I suggest you read Microsoft SQL Server 2012 T-SQL Fundamentals to learn more about DATE, TIME and DATETIME data types. I have referred that book before writing this article and it helped me immensely to fill the gap in my knowledge.

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 datetimecourse_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 2012 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 alwayspull 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.

How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates
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

本文数据库(mssql)相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

主题: SQLMySQLPostgreSQLJavaC++SQL ServerTI
tags: date,SQL,will,course
本文标题:How to Compare Date in SQL Server Query? Finding All Rows Between Two Dates

技术大类 技术大类 | 数据库(mssql) | 评论(0) | 阅读(437)