All right.
Welcome back.
So in this video, we're continuing with dates.
Not the most exciting topic potentially, I know, but an important one nonetheless.
So we're talking about formatting dates right now.
We have dates, times, date times, all stored in a somewhat clunky, condensed, but difficult to
read, at least for human difficult to read format.
It's not very nice.
So how can we extract things out of, let's say, a date time?
How can I convert that so that it says something like April 21st, like the full month name followed
by a space 21st to one T and then the full year rather than 2017.
Dash 0422.
So we're going to do that.
First thing I want to show you, though, is that in the documentation, there's a pretty massive list
of date and time functions.
And I will be honest with you, most of these I never use and you likely will never use that won't stop
me from showing some of them to you.
And I'm going to try and highlight which ones are most important.
And the first show I'm going to show you, I don't actually think are that important, but they are
nice to know that they exist and they're a set of these one off little functions.
I'm not sure even what to call them, but basically there's a group of them, at least in my mind,
that grouping together things that will extract an individual piece of information like day or day name
or day of month, day of week, day of year, our month minute.
So let me show you what I mean.
So I'm going to hop over to Cloud nine and we have a select star.
Let's only work with birth date for now.
So we'll do select name, comma, birth date from.
People.
So instead of displaying the date here or the entire date, what I can do is use some of those things
that we talked about, whatever that group is, let's try and day.
You'll see what it does.
So rather than birth date, I'll select Day.
Of birth date.
And all that it does is it extract the day.
So if we compare, actually what might be better is if I keep birth date and I just add in an extra
column with day.
So it's extracting the day 1125 and 21.
So there's other things we can do.
Let's just go with this list to have up here.
Day name.
And this is a bit more useful.
There are times when you want to do this.
As you can see, we're extracting that.
So it turns out this is a Friday.
This is a Saturday.
This is another Friday.
I know some people can figure this out mentally.
There's a whole trick to doing it.
I'm not one of those people, so we just have to trust it that this is indeed a Friday.
Moving right along, we can try day of week.
And you might wonder, how is that different?
Well, it gives you a number.
So a Friday is a six, Saturday is a seven, a Sunday is a one and so on.
And then we also have day of year.
And that will tell us, starting from the beginning of the year, how many days was it?
Intel.
Oh, boy.
Messed up the parentheses.
Here we go.
Until that date.
So for something.
On Christmas of 1943, it was the 359th day of the year.
Oh, my God.
Blue.
Come on, blue.
And this, of course, takes into account things like leap years.
So important to know.
With that said, these also apply to date times.
So if you tried to do it on time, we start right there instead of doing birth date and you try to do
birth time.
Well, there's going to be an immediate problem, which is we're not storing day of the year or day
information at all.
But just to show you, we don't get an error, right?
We get warnings, but we don't get something that breaks everything.
We just get null.
It doesn't work, but we can do it on a date time.
And that's what, like I said, I use most of the time.
So if we do birth date instead.
You can see that it's giving us the correct numbers here, 359 111 and so on.
So we can use all of the ones we've seen de de name.
But there are other things to briefly that work for date and date time.
So things like month.
So that tells us the month.
What else is there?
I think there's a month name.
Yeah.
So if you want the full name of the month.
There we go.
And then lastly, when we're working with the time.
So if we go and change this to birth time.
There are a few methods that apply here.
We can do things like.
Our if you want to extract the hour or minute.
Now I don't end up using time on its own that often, so I don't really end up using these minute or
hour second on the road ever.
Really.
So that's part one of formatting dates.
Imagine if you wanted to do something like this instead of just printing out the day or something.
We wanted to say April 21st, 2017 at, I don't know, 6 p.m. maybe.
Or we don't even need that.
Let's just say April 21st, 2017.
So how would we take this date?
That would be 2017, dash zero four, dash 21 and convert it to this.
Well, we could use what we've already seen and do a giant Kit-Kat.
I won't make you do this entire thing, but basically we could extract the month name.
To get this and then concatenate that with a space.
And then this part's a little trickier, right?
We need to it's going to be a pain.
Honestly, you would have to have some logic that I haven't shown you how to do yet, because you don't
always add S-T after number.
What if it's a 23rd?
So that's a little bit more complicated.
So let's reduce it to this.
So for that, we would want to get the day right and then we want the year.
So you could do a giant king cat.
Basically, it would take all of these for birth date.
This is going to get messy and the point is that there's a better way.
But imagine you were doing this.
So we have a cat month name of the birth date comma with a space comma, the day of the birth date comma,
another space comma, the year of the birth date.
This is a bit of a mess, but let's see if it does work.
So we'll just select that from people.
There you go.
November 11, 1983.
December 25th, 1943 or 25.
1943.
April 21, 2017.
So it does work, but this is not a great way of formatting dates.
And in fact, there's a much better way which you probably gathered, because the way that I set this
up, there is an entirely separate method or function excuse me that we can use, which is called date
format.
So date format is really, really useful in my opinion.
I use it all the time.
What it does, it looks a little intimidating looking at the documentation, but what it does is it
allows us to format things nicely and powerfully.
We have a lot of different things that we can do.
So these are called specifiers over here.
There's a table of them and they're basically little symbols, little shortcuts that allow us to specify
what we want from that date.
So let's say we're working with a date time and if I want to get the month name, I use percent sign
capital M and if I want the month number, then I use percent sign lowercase M.
If I wanted to get the day of the week as the weekday name, I use percent uppercase w if I want the
day.
The week has a number I use percent lowercase w and so on.
So what we do is combine them together in a string.
And when you pass it into date format, it will take a date.
And then this string of how you want to format it and it will spit out a nice answer for you.
So here's an example.
Excuse my fridge there or my icemaker, I guess being noisy.
So we've got date format of this date time here.
It works on date times and just playing dates.
So here's the date time, though.
My fridge making so much noise.
I think it's done.
So we have this date time and we're formatting it with this, which looks a little bit like gibberish,
but we have percent uppercase w which gives us the day of the week as the word, the name of the day
of the week, and then a space which gives us that space.
Then we have a percent uppercase M which is the name of the month.
So we have October and then another space and then we have percent y which gives us the year.
So really useful and versatile.
I'm just going to just copy this one over.
Just run it so that you can see what happens.
If I hit Enter, we get Sunday, October 29, but we're free to put whatever we want in here so we can
do dashes.
Not that it really makes sense, but if we put dashes there, it still puts the important information
here that we specified.
But now they're separated by dashes, so it gives us some freedom.
We can do group them together like that.
If we wanted to put commas or we could put an entire word before or after, we can do whatever we want.
So this is really, really useful.
If we go back and select let's work with date time.
So we'll do select date format.
And then we're working with birth date.
And then some string that will we'll provide in just a second.
We'll come back to that.
From people.
We can start with something really simple, like just getting the day of the week as a Yeah, we'll
do day of the week first as a word.
So uppercase w.
We could do something like.
Was born on a Friday, a Saturday, a Friday.
But as you saw, there are a bunch of other things that we can do here.
You know, we can work with hours in minutes.
We can work with seconds month names, tons and tons of things.
It's really versatile and very useful.
And typically the things that I end up doing with it are just formatting dates so that they look like,
you know, at least in the US where we put the month first, followed by the day, followed by the year.
So that's pretty simple to do.
Think about how you would do it though, if you want to give it a shot.
How do we take this and change it into that so we can do that now?
All that we want is the month number, right?
So that's percent M I believe followed by a slash and then the day percent dx followed by the year,
which is percent y.
Let's see if that works.
Almost.
If you notice, I use lowercase y and that gives us the two.
I don't know what you call this abbreviated here.
The last two digit digits of the year.
So we want uppercase Y.
And I just happen to remember that.
But if you couldn't remember how to do it, you can consult this.
And we'd find it.
Where's that year numeric four digits versus two digits.
So let's wrap all this up.
We talked about a lot of different ways of formatting dates.
I frankly don't use these that often, like I said, because you can do the same thing using date format.
I should mention there's also a time format.
It works exactly the same way.
You just pass in different argument, you pass in a different format here.
So you're not going to be able to get month, day and year from a time, but that you can get hours,
minute, seconds.
But like I said, I'm not working with times as often.
And if I am using a time component, it's within a date time.
So I might do something like this.
At and let's say.
Let's just do hour and minute and we'll put a colon there.
And there you go.
So I'll do something like that.
But I'm rarely working with just at time.
But there is a time format if you do need to do that.
So back to where I was wrapping up here, there are all these weird one off functions.
I don't use them though because you can just replicate it using date format and it's much easier for
me just to remember one function date format rather than 15 separate functions.
So that's the basics of formatting dates.
Next up, we're going to talk a bit about doing math with dates.