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

Sharing buttons:


hey joy blue here and today I want to

talk about the sequel date comparison

how to filter date time in sequel server

well you might think how hard can it be

you simply put it in the where clause

well there's a little trick to it and I

want to show you that so let's first

take a look at the employee table so I'm

going to select everything from the

employee table and in particular I want

to look at the hire date column so on

the hire date column you can see that we

don't really have a time on there and

that's going to be very significant but

I want to bring back just the person

that's hired or any of the people that

are hired on February 22nd of 2005 so in

order to do that I need to go in here

and actually put on a little filter so

I'll say where hire date equals and then

I want to say oh two for February 22nd

2005 and so let me execute that and I

pulled back the record but now the real

question is what happens if we have a

time attached to this date because in

sequel server there's you know this this

going to is the date time so there's a

date and the time so let me go in and

make that happen so I'm going to go in

and edit the top two and it rose there's

only like nine rows in this table or a

few more and I'm going to find my record

here and I am going to put some time on

there so maybe he was I hired at 8:00 in

the morning 8:00 22 and 25 seconds okay

so I update that record I'm gonna close

this window and we'll rerun this query

and I don't get the record so this is

where the actual problem is and this is

what I'm doing the video is how do you

pull back everybody that was hired on

that day when you have a column that

actually has the time on it also and

there's a couple tricks to do in that

let's start with using a between filter

so basically what we could say instead

using between we can say we're higher

date is bigger than or equal to two 22

2005 and then we can say and higher date

is less than 0 to 20 23 2005 and so what

this is really saying is well let's

let's run it first and I'll tell you

what's really saying what it's really

saying is let me grab this out it's

really saying that that we want

everything bigger than this 0 0 0 0 and

then we want everything less than this

so that is what will I need to fix this

part too sorry so the previous query

actually was saying this here it was

saying we want the hire date bigger than

or equal to february 22nd 2005 at 0

seconds into the day and then we want it

less than february 23rd 2005 at 0

seconds so that pulls back our record

and then there's a couple other ways to

do it which i don't really want to show

you um but here I'll pull over the

screen this is from oh this is from what

is it Stack Overflow and some ask the

same question so there's a couple

different ways to do it they said one is

the best fastest way so you have to

worry about performance if you're on a

big database and the fastest way would

be to use the day tag add function and

let me go ahead and just show you how to

use that real quick and so to use the

date add function you would actually

come in and and I just copy that and

I'll leave it I have a link in the

description for it I would put higher


instead of get date here you put our

date in and you say where it's equal to

your date and so here is that query and

so let's see what happens when I run

that and what did I do

where date add is it did I not copy

everything so all right let me go back

here see if I copied everything that

syntax is not correct is it this person

has the syntax right so let me go back

let's do that one more time so toss the

hire date in there equal to there we go

and so this is what they say is the

fastest way to do it and this this

basically what it's doing is the date

diff it's going out there and saying

from the zero time so that's the

beginning of time to the current date

how many days is that that's what that

DD means and so however many days that

is which we could actually get if we

want it to by describing it here let me

pull this out also so there's

thirty-eight thousand four hundred and

three days since the beginning of time

for this date and that date is actually

February 22nd of 2005 and so then it

says well I want to take the difference

I'm sorry this says I want to add that

many days on to the beginning of time so

basically this strips out the the time

portion of the date time and then it can

does the comparison now the reason they

say this is fast is because they get

into how it talks about floats and

you're not converting the strings and

all the

different things but that would be the

fastest but there is a special technique

that you can use now in 2008 and 2012

because they actually have a new data

type they have a type that's a date and

they have a type that's a time and they

have the date time so they only used to

have the one that's the date time

together but now they have a date data

type so what we can do with that let's

pull this out and this lets redo that so

so in order to do the data the one for

the the new date data type we simply go

in and get get this cast function let me

pull this back and we basically let me

get rid of other stuff I'll give you a

good query and we need our hire date in

here so now we simply cast it as a date

and that will take the time off of it so

let's see how that works we don't need

this little thing up here anymore either

clean up our query so that's a nice

clean query execute that and you can see

that works also so those are really the

different ways of doing it in order to

go get a date that also has a time

involved I would suggest if you're in

2008 or 2012 sequel server to use this

last way it's the fastest and easiest

way to do it so as always if you find

this helpful please leave a comment

below click the like button or subscribe

to my channel you can also visit me at

sequel training online.com and I hope

you enjoyed this free video