SQL Date Comparison - How to Filter Datetime in SQL Server

Video: SQL Date Comparison - How to filter Datetime in SQL Server - SQL Training Online

In this video, I show you how to do a SQL Date Comparison in SQL Server when your Datetime has an actual time in it. I also include the latest trick that will make this easier if you are using SQL Server 2008 or SQL Server 2012.

[/sharebox5_no_text]

How do you compare a Datetime in SQL Server?

A SQL Datetime stores both Date and Time. So when you are filtering using the SQL Where clause, you have to know if you Datetime is storing the time also.

Let-s take a look at the employee table in SQL Training Online Simple DB

select *

from employee

The column we are looking at is the **hire date**. I want to go after the person who was hired on February, 22 2005.

So, I use the following SQL.

select *

from employee

where hire_date=‘02/22/2005’

Now, this works because we don-t have any times stored in this column. But, if I change the record from **2005-02-22 00:00:00.000** to **2005-02-22 08:22:25.000** and rerun the query, you see that I don-t get any results.

![SQL Date Comparison](../../assets/blog/2012/10/DatetimeFilters_thumb.png)

That is because the value of **02/22/2005 is really 2005-02-22 00:00:00.000** and I just changed the hire_date to include a time other than 00:00:00.000.

So, I have to tweak my query to use a greater than and less than sign.

select *

from employee

where hire_date >= '02/22/2005'

and hire_date < '02/23/2005'

This works. But, to really understand what is going on, let me show you what SQL Server really sees.

select *

from employee

where hire_date >= '2005-02-22 00:00:00.000'

and hire_date < '2005-02-23 00:00:00.000'

There are other ways, and in this StackOverflow article you can read more about these other ways. But, I want to point out a few.

If you are working in SQL Server 2005 or earlier, the following is supposed to be the fastest performing way to do this.

select datediff(dd,0, hire_date),*

from employee

where dateadd(dd,0, datediff(dd,0, hire_date)) = '2005-02-22'

But, if you are working in SQL Server 2008 or SQL Server 2012, they have introduced an actual Date datatype that doesn-t include the Time portion. Since this is the case, you can use the CAST function to remove the time from the Datetime.

Here is the SQL to do that.

select *

from employee

where CAST(hire_date AS Date) = '2005-02-22'

And that-s it. That is the SQL Date Comparison in Microsoft SQL Server.

Let me know what you think by commenting or sharing on twitter, facebook, google+, etc.

If you enjoy the video, please give it a like, comment, or subscribe to my channel.

You can visit me at any of the following:

SQL Training Online: /

Twitter: http://www.twitter.com/sql_by_joey

Google+:

LinkedIn: http://www.linkedin.com/in/joeyblue

Facebook: http://www.facebook.com/sqltrainingonline