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 days.
Right now we have dates times date times all stored in a somewhat clunky condensed but difficult to
read at least for human.
It's difficult to read format just 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 Twenty-First to 1 S T and then the full year rather than
you know 2017 dash 0 4 dash 2 2.
So we're going to do that first thing I want to show you though is that in the documentation there is
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 two 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 grouped together things that will extract
an individual piece of information like day or day name or day of month day or week day of year hour
month minute.
So let me show you what I mean.
So I'm going to hop over to cloud 9 and we have a select star.
Let's only worked with birth date for now so we'll select name comet birth date from.
OK.
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 trying to 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 extract the day.
So for you compare actually what might be better.
And if I keep a birth date and I just add in an extra column with day so it's extracting the day 11
25 and 21.
So there's other things we can do.
Let's just go with this list to have a peer 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 turns out this is a Friday.
This is 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 Friday is a six Saturdays seven Sundays one and so on.
Then we also have day of year and that will tell us starting from the beginning of the year.
How many days was at Intel.
Misstepped the parentheses.
Area until that date.
So for something on Christmas of 1943 it was that 350 the day of the year my cat blue.
Come on Blue.
And this of course takes into account things like leap years.
So important to know.
With that said these also applied to date times.
So if you tried to do it on time we start right there instead of doing first date.
And you tried to do first time while others never 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 no 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 D.T. Instead you can see that it's giving us the correct numbers here.
3:59 right 11 and so on.
So we we can use all of ones I've seen Day Day name but there are other things too 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 yet.
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 changes 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 their own effort really.
So that's part one of formatting dates.
Imagine if you wanted to use something like this instead of just printing out the day or something.
We wanted to say April 21st 2017 at 6:00 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 0 4 dash 21 and convert it to this.
Well we could use what we've already seen and do a giant Concat.
I won't make you do this entire thing but basically we could extract the month name
get this and then concatenate it out with a space and then this part's a little trickier right.
You need to.
It's going to be a pain.
Honestly you would have to have some magic that I haven't shown you how to do yet because you don't
always add as T after a number.
What if it's the 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 that year.
So you could do a giant Concat.
Basically it would take all of these birth date.
It's just going to get messy.
And the point is that there's a better way.
But imagine you're doing this.
So we have a concat month name of the birth date comma with a space comma the day of the birth date
comma another space comma that year or if the birth date is a bit of a mess.
But let's see if it does work so we'll just select that from people.
There we go.
November 11 1993 December 25th 1943 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 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 the 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 percents on lowercase m if I wanted to get the
day of the week as the weekday name I use percent upper uppercase w if I want the date that week has
number is percent lower case 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 the string of how you want to format
it and spit out a nice answer for you.
So here's an example.
Use my fridge there my ice maker I guess being noisy.
So we've got date format of this date time here.
It works some day times and just plain days.
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 jibberish
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 present upper case 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 2009 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 important information here that
we specified.
But now they're separated by dashes.
So it gives us some freedom we can do you know group them together like that if we wanted to put commas
or we can put an entire word before 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 T and then some string that will 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 has a yellow
to do day of the week first as a word so uppercase w
we could do something like was born on a Friday Saturday or Friday.
But as you saw there are a bunch of other things that we can do here.
Know we can work with hours and minutes.
We can work with second 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 data 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 good month number.
Right.
So that's percent Am I believe followed by a slash and the day present D followed by a year which is
percent why and see if that works.
Almost If you notice I use lower case Y and that gives us that two.
I don't know we call this the abbreviated year the last Dugit digits of the year.
So we want uppercase Y and I just have to remember that.
But if you couldn't remember how to do it you can solve this and you'd find it.
Where's that your numeric 4 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 or you pass in a different format or here.
So you're not going to be able to get month day and year from a time that you can get you know hours
minutes seconds.
But like I said I'm not working with times as often and if I am using a time component within a date
time so I might do something like this
at let's say let's just do hour and minute and we'll put a call in there and there you go.
So I'll do something like that.
But I'm rarely working with just a time but there is a time format if you do need to do that.
OK.
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 tape format and it's much easier for
me just to remember one function date format rather than 15 separate functions.
So that's the basic formatting dates.
Next up we're going to talk a bit about doing math with Dade's.