So far we've seen a lot with comparing numbers.
Let's talk briefly about comparing dates and times and date times and time stamps.
We can use the operators we've already seen like less than or greater than greater than or equal to
to make comparisons.
Let's work with our people table.
So let's do a select start from people from earlier.
When I covered dates and date times and times, let's say that I want to select the people who were
born before the year 1999.
Or how about before 2005?
Let's do that.
So to do that, I would do a select star or whatever I want to select from people where, and then I
can take the birth date column, which is a date, right?
It's going to be in this format of 2000 and something or whatever the date is.
And I'm going to compare that to where it's less than 2005.
And then what, January 1st of 2005.
And this is a string, right?
This is not a date, but it's a string.
And most of the time, Miko can figure out how to compare an actual date to a string that looks like
a date.
And it does work.
We get the two people, Elton and Lulu, who were born before 2005, January 1st, right.
So 2019 85.
So that does work.
But another option that is more foolproof is to first extract the year from birth date, if that's all
I care about, right, year from birth date and say was the year before 2005.
And we get the same two results.
But this is a lot more explicit and it has fewer places.
It could go wrong.
This relies on my SQL recognizing this as a date and it being in the correct format.
And if I only care about the year, then it's better to extract the year.
Again, this will work most of the time, but it's not always guaranteed to work.
And in fact, the docs make a note about just this very topic, which I'll show you in a moment.
We could also work with times, right?
If I wanted to know if somebody was born after nine in the morning, I could do this.
So star from people where I think we call it birth time is greater than.
And then I would need to have my date string in the correct format.
So something like 090000 that would work.
And ah, all these people born after nine in the morning.
I guess so.
So let's change it to narrow it down a bit.
How about people born after noon, after 12?
So these two people, Juan and Hazel, that does work.
But again, we're comparing a true time to a string.
So if I just want to know if they were born after 12:00, I could do a similar approach where I say
the our portion of birth time.
Remember, we saw all these time functions and date functions.
That's going to give me a number.
It's going to extract the hour and that I can compare that to nine or 12 or whatever we said and we
get the same two.
And there is one more option that I'm going to show you.
There is a function built in to my SQL called caste.
Caste helps us convert one data type to another so I could cast a string into an actual date, for example,
or caste a date time into a date and things like that.
So the format is that we cast some value as some type.
So I would do something like this.
Select cast.
Let's take the string 090000.
Now I hit enter too soon.
All right, let's try that again.
Let's take the string.
090000.
And cast that as a time.
Because this is a piece of text.
It is not truly a time.
It could just as well be Hello world in those quotes.
But here we're telling my SQL.
We want it to be turned into a time and it's not going to look any different when it's printed out,
although I suppose it might look different if I did something like this, right.
I don't have that leading zero.
Well, it can figure out that we should have it and it formats it into the correct time.
I know it's confusing because it looks pretty much the same, but as far as my SQL knows, this is a
piece of text.
Now we've told it, it's a time.
And so what we get back from this cast is a time.
So the reason I bring this up is we can use this when we're making comparisons.
The docs recommend that you do this when you're working with between.
If I wanted to find people who are born in the afternoon, so people who are born between, let's just
do select star from people between 12:00 and I don't know, 4:00 or if we use 24 hour time, what is
that from 12 to 16?
So select star from people and I could do where and then birth time between.
And then I have my first hour would be 12:00.
And and then 16:00 like that and it works.
We find the one person, Hazel, who was born at 14 5829.
So this does work.
It's one of those things where it's almost always going to work, thanks to my SQL, figuring out what
we want.
But the technically correct way of doing this is to turn these strings into times, and we can do that
using cast so we can cast.
And I think I'm actually going to do this in my editor just so you can see it all and not have it wrapped
around on a line and getting cut off.
So we do cast this time.
As an actual time, right?
Because this is not a time data type, it's text, it's a string.
And then we'll say and cast what was our other time?
16:00 I think as a time.
Now this is going to seem like a lot of work for something that already was working with fewer characters.
And that is true.
I'm just being thorough here, but we'll get the same result.
And if all we actually cared about was the hours portion, then what I think is easiest is to just extract
the hour portion from birth time and then compare that to a number.
I don't have to write these long date or time strings.
I could just do what we did earlier.
Select Star from people.
Where birth time is between and instead of all of birth time, I want the hour portion of birth time
that gives me a number.
And where is that?
Between 12 and 16.
So I think this is simpler.
But if you needed to have
1231 and 30 seconds, then this syntax would work great because you can be way more precise with your
times and minutes and seconds.
It's not just limited to one aspect.
But anyway, let's run this and just make sure that works too, and we get the same result.
So there's a lot of comparisons we can do with dates, times, date times, time stamps.
I didn't even show date times and time stamps.
But in general, what I always recommend is make the simplest comparison that you can.
If you do, you need to care about seconds and minutes or even if you have a date time.
Do you care about the time portion or just the date portion?
If I wanted to know if somebody was born before this date, well, this is a date time, but I could
just compare to the date portion.
I don't really care about the time unless I need something that detailed.
I need to know about the time as well.
So we can cast as we saw.
You're not going to notice a difference most of the time because my SQL can figure out that when we
don't cast this to a time, it can tell that this conforms to the time syntax, the correct format and
birth time is a time and it compares them for us.
But this is the technically correct version and it's what the docs recommend you do.