All right, we're almost there.
I've got a tiny bit more about formatting dates and times.
I'm devoting one standalone video to a particular function called format date.
But before we get there, here's a situation that it might help us with.
Let's say that I want to for each person or people row in our database, I want to get a their birthday
in this format.
I want to see the month name like April, and then I want to have maybe a comma and then I want the
day name.
So let's just keep it even simpler.
We don't need the comma.
Let's just say I want it to be April and then 11th, 1985.
I want it to be in this format instead of this format.
So we could do this with what we know.
We know how to get April, right?
I would do month name.
I mean, I have to select it, but select month, name of birth date and then comma, I could get the
date or the day.
So that's day of birth, date, comma, year of birth date from people.
Let's make sure that part works.
Now, it's not going to be one value.
This will come back to me as three different columns.
Right.
But we have April 11, 1985.
So then if I want to smush it together to form one date, well, I can concatenate it and it's going
to be kind of nasty, can cat?
All of this together.
But of course now there's no spaces.
So I need to add those empty spaces in there so I could come in here and add a space comma and then
a space comma.
And that will work.
It's just a little clunky to do it that way.
As you can see, it's a really long single call to con cat, and if I wanted to change things, I've
got to move a lot of text around.
I'm calling these functions nested in here.
It's just not a great way of getting a longer formatted date.
Or the same would go for time if I wanted to get the hour minute in second, but display them differently
instead of this format.
I could use Tomcat in all the different functions we've learned, but there's a better way.
There is a built in function called date format and Dave Format is expecting us to pass through a very
special string called a format string.
And within this format string we have all these different specify or characters or specify our sequences
that we can use.
So I think the best way to do this is just to show you an example right here, we've got percent assigned
B that means nothing to us or to me as a human.
But in this format function, if I use percent B in a string, that will be replaced with the abbreviated
month name for a given date.
So let me show you that.
Let's take the date format function.
So I'll do select date format.
And then within here, I have to pass through two things the date that I'm trying to format and then
my format string.
So let's format birth date, and then our format string will start with just that percent B sequence
that I showed you from people.
And let's also select the birth date.
On its own first.
So when I run it, this is what we see the abbreviated month, and that's because I used percent B if
I tweaked that and I instead did percent C that gives me the month in a numeric format, like 12 or
11 or ten.
There's a whole bunch of other things in here.
As you can see, I can get the full month name with a capital M, so percent capital M So why don't
we do the abbreviated month name, but why don't we get the weekday first, which is nifty.
We can do percent A to get the abbreviated weekday or to get the full weekday name somewhere down here
percent Capital W So I don't remember any of these or almost any of these, but it doesn't matter.
You come to the docs and if you know that you're trying to get some particular format, then you can
find the corresponding sequence that you need to use.
So let's try this.
Let's do percent a first.
And then I can put a space in there, a comma, a dash, whatever.
I want to separate them.
So I'll just do a space.
For now, I need my semicolon.
And there we go.
We see Moon, December, blah, blah, blah.
So why don't we keep going and get the actual date?
If I want the day of the month, I can either get one that is padded with zeros, so like zero nine
or just this version with percent E which doesn't have a leading zero.
I can even get the day of the month with the English suffix.
So that's kind of nifty.
First, second, third, 15th, that sort of thing.
Why don't we go for percent e.
And I'll put a space there as well.
And I keep forgetting my semicolon.
And there we go.
We get mun de sem dec das 25.
If I change that to percent uppercase rd, we'll get that English suffix.
We'll try that.
Let me put my semicolon in so I don't keep having that same problem.
And now we get 25th, 11th, 15th.
All of them have t h at the end, which is sort of annoying.
I don't have any first or second or third, but it is what it is.
So I think that's probably enough examples working with date.
But we also have different signifiers or specifiers.
4 hours, 4 minutes, 4 seconds.
You can see them all listed here.
So percent SE is 4 seconds and we can combine them all together to get whatever formatted date or time
that we want.
There's also a if I go back here, a time format function, oops, come back.
There is a time format.
It's alphabetical.
Where are you?
Right here.
And this works the same way, but it is only going to allow you to use the format specifiers that are
valid for a time.
So you can't use day of the week or month name or anything, but you can use hours, minutes, seconds
and so on.
Date format supports all of it, so I could format the date, time and extract.
I don't know.
How about the hours and minutes from my date time?
Let's try that.
Select Birth Date.
Comma.
Date Format.
Birth Date for date time.
I'm formatting the date times now and my string will have percent.
I believe it's h 4 hours, but we can always double check.
There's different variants.
Do I want to start go from 0 to 23 or am I doing PM?
Am pm time?
Right.
So 1 to 12, let's go with 24 hours.
So Capital H.
And then I want the minutes afterwards and that is I percent I.
So we'll do percent I right there.
OC.
From people.
We'll try running this one.
And we see from each date time we got the hour and the minute and we put a colon in there, but we could
put whatever we want and we can even add fancy things on like AM and PM.
So if we wanted to do 12 hour time.
Where is that?
Well, there's actually a shortcut which is just percent ah is 12 hour time.
Let's take a look at that.
If I just replace this whole thing with percent.
Ah.
We get the full time, but with a AM and PM.
But it is in 24 or in 12 hour time.
So this 2359 became 11:59 p.m..
So that is the date format function.
And again there's a time format which you can use, but you're restricted to the time.
Different specifiers like minutes and seconds and hours.
You can't use any of the date stuff, but either way they follow the same pattern.
You provide the value or in our case, the column we're trying to format, and then the format string,
which consists of one or more of these specifiers along with whatever we want.
I mean, I can do something like Born on if I wanted to, and I would format it this way, and that
is my resulting formatted string.
Each one of these special signifiers will be replaced with their corresponding piece of information
that has been extracted out, and it's really powerful.
We can get, as we saw the English suffix day of the month, or we can get the date of the week or the
abbreviated day of the week, the abbreviated month name, the full month name and a lot of information.
So that is date format.