All right.
Let's talk about some of the more useful date functions.
There's a pretty significant list of them.
There's I don't know, a couple of dozen in here on this page, date and time functions.
We are not going to hit all of them, although we've knocked this chunk out already.
We're just going to talk about in this video some of the functions that pertain to formatting dates
or extracting a portion of a date or a time into a different format.
For example, right now with our four people, let's say I want to work with the birth date.
So I would do select birth date from people, but this format is not exactly readable.
I mean, it's fine, it's not that complicated, but maybe I just want the year or just the month,
the month number or the month as a word like December, April, August.
What about the day of the week?
I could get the day 25, but what if that was a monday?
I want to know that.
Or was that a Saturday?
Was it a Sunday?
You know, we can find this information out using different functions that are built in.
So the first one that we'll look at is how about De Day is a synonym for day of month and it will return
to us if we go look at day of month, just the day of the month for a particular date.
So it's pretty straightforward.
This is a simple one to start with, but let's try select.
Why don't we select the birth date?
And day of that birthday.
So we pass in.
Just like with our string functions, we pass in a column name and you'll see that we just extracted
into its own piece of data on its own the day 25 or 11, or 15 or five.
But I can go further.
I can do I believe it's right below on the docs day of week, and that will tell us the day of the week
where one is Sunday all the way up to seven being Saturday.
So let's try that day of week.
Four birth date.
And we see, too.
So I believe that's Monday, right?
If one is Sunday, two is Monday up to seven.
So this person here, we should probably put their name in.
But whoever this is, born on August 15th.
That was a Saturday.
So we can get that day of the week.
We can do things like get the day of the year, if I want to know it.
Was it the 50th date of the year?
The 300th date of the year.
So you can see a pattern developing here.
Hopefully where we've got a lot of this information we can extract just from a single date using these
different functions.
So day of year.
And then we pass in our column like birth date.
And here we are.
This one makes sense, right?
Christmas towards the end of the year, December 25th.
That was the 360th day of that year.
There's a lot of math and stuff going on behind the scenes.
Working with dates and times generally is a pain in the world of programming, but fortunately, we
don't have to worry about any of that.
My SQL gives us all these different functions, so there's a lot more.
Let's look at just the list again.
And how about we work with month name?
So month name will give us the actual name of the month.
Let's just do select birth date and then month name of that birth date.
From people.
And we see December 12 is turned into December, April, August, October, just like with our string
functions, we're not changing anything in the database.
We're not inserting or updating.
We're simply extracting some information and displaying it based off of what is stored.
So these queries are running have zero impact on the database or the underlying rows.
We're just formatting it when we look at this, see the month name.
All right, so we have month name.
We even have if I keep going down here somewhere we have weak.
This will tell us the week number is the first week, the second week, the third week of the year.
I won't make you watch me do that because by now you probably understand how these work.
But what I want to show is what if I tried to do something like pass through birth time instead?
Remember, birth time is a time type, so it's not going to work for me.
Why is it showing me October for all of these?
I believe it's just assuming based off of the current month, which is October, I'm recording this
in October 2022.
So either way it's wrong, right?
It's just saying October for all of these times.
It's assuming just with what little information it has that these times are taking place today, but
otherwise it has no idea.
Same thing if I did something like so I can extract the year.
But it's just going to give me 2022 because again, there's no date information here.
It's only a time.
But if I worked with the date time, so this time why don't we get the name just so we have a name to
put to each date name and then birth date?
Well, then I can do things like get the year from that birth date because there's a date portion,
of course, in a date time.
This has the date and the time, but I could also do things like get the month name.
Of birth, date time.
There we are.
And I'm getting the month name.
December, April, August, October.
So both dates and date time, it probably goes without saying, are going to work with all these date
functions year, day of the week, month, name, month, day of the year, week of the year.
All that stuff that is specific to dates also works with date time.
In the next video, we'll talk about time.