1
00:00:00,180 --> 00:00:00,830
All right.

2
00:00:00,840 --> 00:00:01,620
Welcome back.

3
00:00:01,650 --> 00:00:04,170
So in this video, we're continuing with dates.

4
00:00:04,350 --> 00:00:09,320
Not the most exciting topic potentially, I know, but an important one nonetheless.

5
00:00:09,330 --> 00:00:11,880
So we're talking about formatting dates right now.

6
00:00:11,880 --> 00:00:18,990
We have dates, times, date times, all stored in a somewhat clunky, condensed, but difficult to

7
00:00:18,990 --> 00:00:22,080
read, at least for human difficult to read format.

8
00:00:22,190 --> 00:00:23,100
It's not very nice.

9
00:00:23,100 --> 00:00:26,430
So how can we extract things out of, let's say, a date time?

10
00:00:26,430 --> 00:00:34,830
How can I convert that so that it says something like April 21st, like the full month name followed

11
00:00:34,830 --> 00:00:43,380
by a space 21st to one T and then the full year rather than 2017.

12
00:00:43,380 --> 00:00:45,660
Dash 0422.

13
00:00:46,050 --> 00:00:47,850
So we're going to do that.

14
00:00:47,850 --> 00:00:53,100
First thing I want to show you, though, is that in the documentation, there's a pretty massive list

15
00:00:53,100 --> 00:00:55,080
of date and time functions.

16
00:00:55,080 --> 00:01:04,170
And I will be honest with you, most of these I never use and you likely will never use that won't stop

17
00:01:04,170 --> 00:01:05,850
me from showing some of them to you.

18
00:01:05,850 --> 00:01:09,360
And I'm going to try and highlight which ones are most important.

19
00:01:09,360 --> 00:01:13,650
And the first show I'm going to show you, I don't actually think are that important, but they are

20
00:01:13,650 --> 00:01:19,170
nice to know that they exist and they're a set of these one off little functions.

21
00:01:19,170 --> 00:01:24,980
I'm not sure even what to call them, but basically there's a group of them, at least in my mind,

22
00:01:24,980 --> 00:01:32,070
that grouping together things that will extract an individual piece of information like day or day name

23
00:01:32,070 --> 00:01:38,700
or day of month, day of week, day of year, our month minute.

24
00:01:39,390 --> 00:01:40,860
So let me show you what I mean.

25
00:01:41,100 --> 00:01:45,030
So I'm going to hop over to Cloud nine and we have a select star.

26
00:01:45,060 --> 00:01:47,700
Let's only work with birth date for now.

27
00:01:48,120 --> 00:01:54,450
So we'll do select name, comma, birth date from.

28
00:01:55,220 --> 00:01:55,880
People.

29
00:01:57,770 --> 00:02:05,150
So instead of displaying the date here or the entire date, what I can do is use some of those things

30
00:02:05,150 --> 00:02:08,780
that we talked about, whatever that group is, let's try and day.

31
00:02:08,810 --> 00:02:10,070
You'll see what it does.

32
00:02:10,610 --> 00:02:14,180
So rather than birth date, I'll select Day.

33
00:02:15,470 --> 00:02:16,430
Of birth date.

34
00:02:17,870 --> 00:02:20,690
And all that it does is it extract the day.

35
00:02:21,080 --> 00:02:31,580
So if we compare, actually what might be better is if I keep birth date and I just add in an extra

36
00:02:31,580 --> 00:02:32,900
column with day.

37
00:02:33,530 --> 00:02:37,460
So it's extracting the day 1125 and 21.

38
00:02:38,300 --> 00:02:40,280
So there's other things we can do.

39
00:02:40,850 --> 00:02:42,860
Let's just go with this list to have up here.

40
00:02:43,190 --> 00:02:44,150
Day name.

41
00:02:45,990 --> 00:02:47,440
And this is a bit more useful.

42
00:02:47,460 --> 00:02:49,200
There are times when you want to do this.

43
00:02:49,830 --> 00:02:51,510
As you can see, we're extracting that.

44
00:02:51,510 --> 00:02:53,400
So it turns out this is a Friday.

45
00:02:53,430 --> 00:02:54,510
This is a Saturday.

46
00:02:54,540 --> 00:02:56,070
This is another Friday.

47
00:02:56,730 --> 00:02:58,860
I know some people can figure this out mentally.

48
00:02:58,860 --> 00:03:00,600
There's a whole trick to doing it.

49
00:03:00,810 --> 00:03:06,360
I'm not one of those people, so we just have to trust it that this is indeed a Friday.

50
00:03:07,770 --> 00:03:11,190
Moving right along, we can try day of week.

51
00:03:11,580 --> 00:03:13,800
And you might wonder, how is that different?

52
00:03:15,390 --> 00:03:16,860
Well, it gives you a number.

53
00:03:17,100 --> 00:03:22,170
So a Friday is a six, Saturday is a seven, a Sunday is a one and so on.

54
00:03:23,040 --> 00:03:25,230
And then we also have day of year.

55
00:03:26,010 --> 00:03:30,000
And that will tell us, starting from the beginning of the year, how many days was it?

56
00:03:30,000 --> 00:03:30,680
Intel.

57
00:03:31,510 --> 00:03:32,250
Oh, boy.

58
00:03:33,000 --> 00:03:34,410
Messed up the parentheses.

59
00:03:36,740 --> 00:03:37,310
Here we go.

60
00:03:38,100 --> 00:03:39,120
Until that date.

61
00:03:39,120 --> 00:03:40,530
So for something.

62
00:03:41,070 --> 00:03:46,680
On Christmas of 1943, it was the 359th day of the year.

63
00:03:46,710 --> 00:03:47,670
Oh, my God.

64
00:03:47,880 --> 00:03:48,180
Blue.

65
00:03:48,210 --> 00:03:50,850
Come on, blue.

66
00:03:51,480 --> 00:03:54,420
And this, of course, takes into account things like leap years.

67
00:03:54,420 --> 00:03:55,800
So important to know.

68
00:03:57,420 --> 00:04:01,530
With that said, these also apply to date times.

69
00:04:01,530 --> 00:04:08,250
So if you tried to do it on time, we start right there instead of doing birth date and you try to do

70
00:04:08,250 --> 00:04:09,420
birth time.

71
00:04:09,420 --> 00:04:15,000
Well, there's going to be an immediate problem, which is we're not storing day of the year or day

72
00:04:15,000 --> 00:04:16,350
information at all.

73
00:04:17,850 --> 00:04:22,440
But just to show you, we don't get an error, right?

74
00:04:22,440 --> 00:04:25,470
We get warnings, but we don't get something that breaks everything.

75
00:04:25,470 --> 00:04:26,670
We just get null.

76
00:04:26,790 --> 00:04:31,860
It doesn't work, but we can do it on a date time.

77
00:04:31,860 --> 00:04:34,950
And that's what, like I said, I use most of the time.

78
00:04:34,950 --> 00:04:38,430
So if we do birth date instead.

79
00:04:39,650 --> 00:04:45,680
You can see that it's giving us the correct numbers here, 359 111 and so on.

80
00:04:45,980 --> 00:04:49,850
So we can use all of the ones we've seen de de name.

81
00:04:49,850 --> 00:04:55,730
But there are other things to briefly that work for date and date time.

82
00:04:55,730 --> 00:04:57,800
So things like month.

83
00:04:59,320 --> 00:05:00,640
So that tells us the month.

84
00:05:01,420 --> 00:05:02,650
What else is there?

85
00:05:02,770 --> 00:05:04,470
I think there's a month name.

86
00:05:04,480 --> 00:05:05,050
Yeah.

87
00:05:06,100 --> 00:05:10,090
So if you want the full name of the month.

88
00:05:10,510 --> 00:05:11,320
There we go.

89
00:05:11,920 --> 00:05:15,670
And then lastly, when we're working with the time.

90
00:05:15,670 --> 00:05:18,790
So if we go and change this to birth time.

91
00:05:22,900 --> 00:05:25,510
There are a few methods that apply here.

92
00:05:25,510 --> 00:05:26,920
We can do things like.

93
00:05:28,630 --> 00:05:32,890
Our if you want to extract the hour or minute.

94
00:05:35,750 --> 00:05:41,930
Now I don't end up using time on its own that often, so I don't really end up using these minute or

95
00:05:41,930 --> 00:05:44,540
hour second on the road ever.

96
00:05:44,540 --> 00:05:45,110
Really.

97
00:05:45,950 --> 00:05:48,110
So that's part one of formatting dates.

98
00:05:48,590 --> 00:05:53,540
Imagine if you wanted to do something like this instead of just printing out the day or something.

99
00:05:53,540 --> 00:06:05,780
We wanted to say April 21st, 2017 at, I don't know, 6 p.m. maybe.

100
00:06:06,230 --> 00:06:07,430
Or we don't even need that.

101
00:06:07,430 --> 00:06:09,860
Let's just say April 21st, 2017.

102
00:06:10,430 --> 00:06:13,700
So how would we take this date?

103
00:06:13,700 --> 00:06:19,280
That would be 2017, dash zero four, dash 21 and convert it to this.

104
00:06:19,280 --> 00:06:23,970
Well, we could use what we've already seen and do a giant Kit-Kat.

105
00:06:24,170 --> 00:06:29,450
I won't make you do this entire thing, but basically we could extract the month name.

106
00:06:32,530 --> 00:06:36,130
To get this and then concatenate that with a space.

107
00:06:36,580 --> 00:06:38,740
And then this part's a little trickier, right?

108
00:06:38,740 --> 00:06:41,280
We need to it's going to be a pain.

109
00:06:41,290 --> 00:06:46,990
Honestly, you would have to have some logic that I haven't shown you how to do yet, because you don't

110
00:06:46,990 --> 00:06:49,300
always add S-T after number.

111
00:06:49,810 --> 00:06:51,520
What if it's a 23rd?

112
00:06:52,390 --> 00:06:54,280
So that's a little bit more complicated.

113
00:06:54,280 --> 00:06:55,750
So let's reduce it to this.

114
00:06:55,960 --> 00:07:01,300
So for that, we would want to get the day right and then we want the year.

115
00:07:02,890 --> 00:07:05,200
So you could do a giant king cat.

116
00:07:05,290 --> 00:07:09,070
Basically, it would take all of these for birth date.

117
00:07:09,940 --> 00:07:13,240
This is going to get messy and the point is that there's a better way.

118
00:07:13,540 --> 00:07:15,280
But imagine you were doing this.

119
00:07:15,280 --> 00:07:24,760
So we have a cat month name of the birth date comma with a space comma, the day of the birth date comma,

120
00:07:25,840 --> 00:07:29,440
another space comma, the year of the birth date.

121
00:07:30,550 --> 00:07:34,150
This is a bit of a mess, but let's see if it does work.

122
00:07:34,450 --> 00:07:38,410
So we'll just select that from people.

123
00:07:39,640 --> 00:07:40,450
There you go.

124
00:07:40,960 --> 00:07:43,060
November 11, 1983.

125
00:07:43,180 --> 00:07:45,940
December 25th, 1943 or 25.

126
00:07:45,940 --> 00:07:46,960
1943.

127
00:07:46,990 --> 00:07:49,150
April 21, 2017.

128
00:07:49,450 --> 00:07:53,140
So it does work, but this is not a great way of formatting dates.

129
00:07:53,140 --> 00:07:58,390
And in fact, there's a much better way which you probably gathered, because the way that I set this

130
00:07:58,390 --> 00:08:05,230
up, there is an entirely separate method or function excuse me that we can use, which is called date

131
00:08:05,230 --> 00:08:05,890
format.

132
00:08:06,550 --> 00:08:09,250
So date format is really, really useful in my opinion.

133
00:08:09,250 --> 00:08:10,300
I use it all the time.

134
00:08:10,990 --> 00:08:17,290
What it does, it looks a little intimidating looking at the documentation, but what it does is it

135
00:08:17,290 --> 00:08:20,650
allows us to format things nicely and powerfully.

136
00:08:20,650 --> 00:08:22,900
We have a lot of different things that we can do.

137
00:08:23,140 --> 00:08:25,780
So these are called specifiers over here.

138
00:08:25,780 --> 00:08:32,289
There's a table of them and they're basically little symbols, little shortcuts that allow us to specify

139
00:08:32,289 --> 00:08:33,909
what we want from that date.

140
00:08:33,909 --> 00:08:41,230
So let's say we're working with a date time and if I want to get the month name, I use percent sign

141
00:08:41,230 --> 00:08:48,760
capital M and if I want the month number, then I use percent sign lowercase M.

142
00:08:49,840 --> 00:08:56,680
If I wanted to get the day of the week as the weekday name, I use percent uppercase w if I want the

143
00:08:56,680 --> 00:08:56,810
day.

144
00:08:56,830 --> 00:09:00,460
The week has a number I use percent lowercase w and so on.

145
00:09:01,870 --> 00:09:06,550
So what we do is combine them together in a string.

146
00:09:07,240 --> 00:09:10,510
And when you pass it into date format, it will take a date.

147
00:09:10,510 --> 00:09:15,970
And then this string of how you want to format it and it will spit out a nice answer for you.

148
00:09:16,000 --> 00:09:17,350
So here's an example.

149
00:09:18,040 --> 00:09:22,480
Excuse my fridge there or my icemaker, I guess being noisy.

150
00:09:22,510 --> 00:09:26,290
So we've got date format of this date time here.

151
00:09:26,710 --> 00:09:29,090
It works on date times and just playing dates.

152
00:09:29,110 --> 00:09:30,730
So here's the date time, though.

153
00:09:33,220 --> 00:09:35,440
My fridge making so much noise.

154
00:09:36,160 --> 00:09:37,060
I think it's done.

155
00:09:38,230 --> 00:09:43,570
So we have this date time and we're formatting it with this, which looks a little bit like gibberish,

156
00:09:43,570 --> 00:09:50,770
but we have percent uppercase w which gives us the day of the week as the word, the name of the day

157
00:09:50,770 --> 00:09:54,520
of the week, and then a space which gives us that space.

158
00:09:54,520 --> 00:09:58,690
Then we have a percent uppercase M which is the name of the month.

159
00:09:58,930 --> 00:10:05,140
So we have October and then another space and then we have percent y which gives us the year.

160
00:10:06,040 --> 00:10:08,620
So really useful and versatile.

161
00:10:08,620 --> 00:10:10,810
I'm just going to just copy this one over.

162
00:10:10,840 --> 00:10:13,420
Just run it so that you can see what happens.

163
00:10:16,400 --> 00:10:23,210
If I hit Enter, we get Sunday, October 29, but we're free to put whatever we want in here so we can

164
00:10:23,210 --> 00:10:25,430
do dashes.

165
00:10:25,460 --> 00:10:31,070
Not that it really makes sense, but if we put dashes there, it still puts the important information

166
00:10:31,070 --> 00:10:32,750
here that we specified.

167
00:10:32,990 --> 00:10:37,040
But now they're separated by dashes, so it gives us some freedom.

168
00:10:37,040 --> 00:10:39,200
We can do group them together like that.

169
00:10:39,200 --> 00:10:46,220
If we wanted to put commas or we could put an entire word before or after, we can do whatever we want.

170
00:10:46,760 --> 00:10:48,530
So this is really, really useful.

171
00:10:49,070 --> 00:10:52,820
If we go back and select let's work with date time.

172
00:10:53,330 --> 00:10:57,260
So we'll do select date format.

173
00:10:59,760 --> 00:11:02,790
And then we're working with birth date.

174
00:11:04,100 --> 00:11:07,630
And then some string that will we'll provide in just a second.

175
00:11:07,640 --> 00:11:08,780
We'll come back to that.

176
00:11:11,590 --> 00:11:12,490
From people.

177
00:11:13,660 --> 00:11:21,430
We can start with something really simple, like just getting the day of the week as a Yeah, we'll

178
00:11:21,430 --> 00:11:23,140
do day of the week first as a word.

179
00:11:23,140 --> 00:11:24,880
So uppercase w.

180
00:11:27,830 --> 00:11:29,240
We could do something like.

181
00:11:30,990 --> 00:11:36,780
Was born on a Friday, a Saturday, a Friday.

182
00:11:38,280 --> 00:11:41,430
But as you saw, there are a bunch of other things that we can do here.

183
00:11:43,160 --> 00:11:45,590
You know, we can work with hours in minutes.

184
00:11:46,370 --> 00:11:51,320
We can work with seconds month names, tons and tons of things.

185
00:11:51,680 --> 00:11:54,050
It's really versatile and very useful.

186
00:11:54,470 --> 00:11:59,840
And typically the things that I end up doing with it are just formatting dates so that they look like,

187
00:12:01,130 --> 00:12:08,180
you know, at least in the US where we put the month first, followed by the day, followed by the year.

188
00:12:10,460 --> 00:12:11,860
So that's pretty simple to do.

189
00:12:11,870 --> 00:12:14,870
Think about how you would do it though, if you want to give it a shot.

190
00:12:15,020 --> 00:12:21,080
How do we take this and change it into that so we can do that now?

191
00:12:22,860 --> 00:12:27,190
All that we want is the month number, right?

192
00:12:27,210 --> 00:12:36,420
So that's percent M I believe followed by a slash and then the day percent dx followed by the year,

193
00:12:37,170 --> 00:12:38,820
which is percent y.

194
00:12:38,850 --> 00:12:39,960
Let's see if that works.

195
00:12:41,400 --> 00:12:42,150
Almost.

196
00:12:42,690 --> 00:12:46,320
If you notice, I use lowercase y and that gives us the two.

197
00:12:46,830 --> 00:12:48,900
I don't know what you call this abbreviated here.

198
00:12:49,080 --> 00:12:51,180
The last two digit digits of the year.

199
00:12:51,540 --> 00:12:53,340
So we want uppercase Y.

200
00:12:54,120 --> 00:12:55,620
And I just happen to remember that.

201
00:12:55,620 --> 00:12:58,950
But if you couldn't remember how to do it, you can consult this.

202
00:13:01,210 --> 00:13:02,230
And we'd find it.

203
00:13:02,230 --> 00:13:06,030
Where's that year numeric four digits versus two digits.

204
00:13:06,040 --> 00:13:08,020
So let's wrap all this up.

205
00:13:08,020 --> 00:13:10,330
We talked about a lot of different ways of formatting dates.

206
00:13:11,170 --> 00:13:18,010
I frankly don't use these that often, like I said, because you can do the same thing using date format.

207
00:13:18,760 --> 00:13:20,770
I should mention there's also a time format.

208
00:13:20,770 --> 00:13:22,600
It works exactly the same way.

209
00:13:23,440 --> 00:13:27,790
You just pass in different argument, you pass in a different format here.

210
00:13:27,790 --> 00:13:33,790
So you're not going to be able to get month, day and year from a time, but that you can get hours,

211
00:13:33,790 --> 00:13:34,810
minute, seconds.

212
00:13:35,410 --> 00:13:37,960
But like I said, I'm not working with times as often.

213
00:13:38,230 --> 00:13:41,710
And if I am using a time component, it's within a date time.

214
00:13:41,710 --> 00:13:43,840
So I might do something like this.

215
00:13:47,240 --> 00:13:49,700
At and let's say.

216
00:13:51,650 --> 00:13:57,080
Let's just do hour and minute and we'll put a colon there.

217
00:13:58,270 --> 00:13:59,170
And there you go.

218
00:13:59,800 --> 00:14:02,260
So I'll do something like that.

219
00:14:02,410 --> 00:14:04,840
But I'm rarely working with just at time.

220
00:14:04,840 --> 00:14:07,900
But there is a time format if you do need to do that.

221
00:14:08,860 --> 00:14:13,540
So back to where I was wrapping up here, there are all these weird one off functions.

222
00:14:13,540 --> 00:14:17,920
I don't use them though because you can just replicate it using date format and it's much easier for

223
00:14:17,920 --> 00:14:23,600
me just to remember one function date format rather than 15 separate functions.

224
00:14:23,620 --> 00:14:25,690
So that's the basics of formatting dates.

225
00:14:26,110 --> 00:14:29,020
Next up, we're going to talk a bit about doing math with dates.