SQL Date Comparison - How to filter Datetime in SQL Server - Quick Tips Ep38

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.

TABLE OF CONTENTS
00:00 - Intro
01:10 - Date Filter with No Time
02:17 - Date Filter with Time using Range
04:20 - Date Filter using DateAdd() Function
07:00 - Date Filter using CAST as Date

DESCRIPTION
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

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.

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.

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

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.

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

READ THE ORIGINAL ARTICLE WITH SQL SCRIPTS HERE

YOUTUBE NEWS UPDATES

VISIT SQLTRAININGONLINE.COM FOR MORE VIDEO NEWS & TIPS

SUBSCRIBE FOR OTHER SQL TIPS AND NEWS!

SUBSCRIBE TO OUR EMAIL LIST!

LET'S CONNECT!

[mxjtube]

Great vid! Simple to understand and concise, and appreciate the datetime code examples too. Thanks!

[skdonsingh]

Convert(date, hiredate, 103)
Wiil provide same output

[software_engineer_semh]

Dear sir, excellent sir , thanks thanks very very thanks sir

[Nikhil-jjxf]

can you please share same video for Oracle as oracle dont have any datediff function, thanks for the video

[robertobernal]

How can i filter data only by their time range (hours) if the date is automatic (GetDate)

[anitaartlett]

Many thanks, you helped me fix my problem :-)

[jovanserania]

Sir, how to print data from date to date?

[sia_ibk]

Hi, I keep getting an incorrect syntax error near the < sign.

[lamiamm]

it was really useful thank you so much!!
but what if i want to remove Date from Time?
should i replace >>> Where CAST( hire_date As Date )
with this >>> Where CAST (hire_date As Time)??
thank you so much i really appreciate it!

[jokshithdsouza]

Hi Joey Blue, what if one person has 2 hire dates ? How can we filter one person with only one date ?
Max function is not effective.
Could you suggest an alternative ?

[smf]

hi sir, id like to ask how can i filter a users log in if he's first time in is 6:00am and he leaves at 3:00pm and then he came for work again on same day which is 10pm until the next day. how can i get the 2 timein of the user?

thanks.

[duraivinoth]

how can i do this same thinks using nodjes bookshelf.

[arnabdas]

what if i try to find employees those who r hired in January or any other months?? :/

[QuarterZipBro]

Great video. I am new to sql queries and have to find a way to do a query where I return results that occurred between the time of 06:00 and 18:00 each day, no matter what the date is. My date/time field is called open_time and is for trouble tickets. I want to only find tickets opened during the hours of 6a to 6pm. How might I build a query?