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

2
00:00:00,810 --> 00:00:04,000
Let's talk about some of the more useful date functions.

3
00:00:04,019 --> 00:00:05,880
There's a pretty significant list of them.

4
00:00:05,880 --> 00:00:10,500
There's I don't know, a couple of dozen in here on this page, date and time functions.

5
00:00:10,500 --> 00:00:14,760
We are not going to hit all of them, although we've knocked this chunk out already.

6
00:00:15,300 --> 00:00:20,880
We're just going to talk about in this video some of the functions that pertain to formatting dates

7
00:00:20,880 --> 00:00:26,460
or extracting a portion of a date or a time into a different format.

8
00:00:26,460 --> 00:00:33,030
For example, right now with our four people, let's say I want to work with the birth date.

9
00:00:33,030 --> 00:00:40,350
So I would do select birth date from people, but this format is not exactly readable.

10
00:00:40,350 --> 00:00:45,780
I mean, it's fine, it's not that complicated, but maybe I just want the year or just the month,

11
00:00:45,780 --> 00:00:51,600
the month number or the month as a word like December, April, August.

12
00:00:51,600 --> 00:00:53,190
What about the day of the week?

13
00:00:53,190 --> 00:00:56,880
I could get the day 25, but what if that was a monday?

14
00:00:56,880 --> 00:00:57,660
I want to know that.

15
00:00:57,660 --> 00:00:58,890
Or was that a Saturday?

16
00:00:58,890 --> 00:01:00,960
Was it a Sunday?

17
00:01:00,960 --> 00:01:06,060
You know, we can find this information out using different functions that are built in.

18
00:01:06,450 --> 00:01:15,120
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

19
00:01:15,120 --> 00:01:21,240
to us if we go look at day of month, just the day of the month for a particular date.

20
00:01:21,240 --> 00:01:22,380
So it's pretty straightforward.

21
00:01:22,380 --> 00:01:26,040
This is a simple one to start with, but let's try select.

22
00:01:26,040 --> 00:01:28,380
Why don't we select the birth date?

23
00:01:29,820 --> 00:01:33,250
And day of that birthday.

24
00:01:33,270 --> 00:01:34,500
So we pass in.

25
00:01:35,340 --> 00:01:40,950
Just like with our string functions, we pass in a column name and you'll see that we just extracted

26
00:01:40,950 --> 00:01:47,490
into its own piece of data on its own the day 25 or 11, or 15 or five.

27
00:01:47,880 --> 00:01:49,320
But I can go further.

28
00:01:49,320 --> 00:01:55,920
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

29
00:01:55,920 --> 00:02:01,110
where one is Sunday all the way up to seven being Saturday.

30
00:02:01,350 --> 00:02:04,290
So let's try that day of week.

31
00:02:06,290 --> 00:02:07,640
Four birth date.

32
00:02:10,520 --> 00:02:12,410
And we see, too.

33
00:02:12,440 --> 00:02:13,850
So I believe that's Monday, right?

34
00:02:13,850 --> 00:02:17,120
If one is Sunday, two is Monday up to seven.

35
00:02:17,120 --> 00:02:19,880
So this person here, we should probably put their name in.

36
00:02:19,880 --> 00:02:23,530
But whoever this is, born on August 15th.

37
00:02:23,570 --> 00:02:25,250
That was a Saturday.

38
00:02:25,820 --> 00:02:27,860
So we can get that day of the week.

39
00:02:27,980 --> 00:02:32,060
We can do things like get the day of the year, if I want to know it.

40
00:02:32,060 --> 00:02:33,740
Was it the 50th date of the year?

41
00:02:33,740 --> 00:02:35,600
The 300th date of the year.

42
00:02:35,840 --> 00:02:38,150
So you can see a pattern developing here.

43
00:02:38,150 --> 00:02:43,070
Hopefully where we've got a lot of this information we can extract just from a single date using these

44
00:02:43,070 --> 00:02:44,030
different functions.

45
00:02:44,030 --> 00:02:45,560
So day of year.

46
00:02:46,390 --> 00:02:50,470
And then we pass in our column like birth date.

47
00:02:51,220 --> 00:02:52,580
And here we are.

48
00:02:52,600 --> 00:02:54,160
This one makes sense, right?

49
00:02:54,160 --> 00:02:57,610
Christmas towards the end of the year, December 25th.

50
00:02:57,820 --> 00:03:00,850
That was the 360th day of that year.

51
00:03:00,880 --> 00:03:04,600
There's a lot of math and stuff going on behind the scenes.

52
00:03:04,690 --> 00:03:09,430
Working with dates and times generally is a pain in the world of programming, but fortunately, we

53
00:03:09,430 --> 00:03:10,930
don't have to worry about any of that.

54
00:03:11,080 --> 00:03:15,130
My SQL gives us all these different functions, so there's a lot more.

55
00:03:15,310 --> 00:03:17,770
Let's look at just the list again.

56
00:03:18,250 --> 00:03:20,470
And how about we work with month name?

57
00:03:20,470 --> 00:03:23,860
So month name will give us the actual name of the month.

58
00:03:24,400 --> 00:03:32,890
Let's just do select birth date and then month name of that birth date.

59
00:03:33,930 --> 00:03:35,370
From people.

60
00:03:36,030 --> 00:03:44,640
And we see December 12 is turned into December, April, August, October, just like with our string

61
00:03:44,640 --> 00:03:47,520
functions, we're not changing anything in the database.

62
00:03:47,520 --> 00:03:49,190
We're not inserting or updating.

63
00:03:49,200 --> 00:03:54,780
We're simply extracting some information and displaying it based off of what is stored.

64
00:03:54,780 --> 00:03:59,280
So these queries are running have zero impact on the database or the underlying rows.

65
00:03:59,280 --> 00:04:02,700
We're just formatting it when we look at this, see the month name.

66
00:04:03,180 --> 00:04:05,130
All right, so we have month name.

67
00:04:05,250 --> 00:04:09,990
We even have if I keep going down here somewhere we have weak.

68
00:04:10,020 --> 00:04:15,510
This will tell us the week number is the first week, the second week, the third week of the year.

69
00:04:15,510 --> 00:04:19,890
I won't make you watch me do that because by now you probably understand how these work.

70
00:04:20,190 --> 00:04:27,750
But what I want to show is what if I tried to do something like pass through birth time instead?

71
00:04:27,780 --> 00:04:33,960
Remember, birth time is a time type, so it's not going to work for me.

72
00:04:33,960 --> 00:04:36,480
Why is it showing me October for all of these?

73
00:04:36,480 --> 00:04:42,990
I believe it's just assuming based off of the current month, which is October, I'm recording this

74
00:04:42,990 --> 00:04:44,460
in October 2022.

75
00:04:44,850 --> 00:04:47,310
So either way it's wrong, right?

76
00:04:47,310 --> 00:04:49,440
It's just saying October for all of these times.

77
00:04:49,440 --> 00:04:56,310
It's assuming just with what little information it has that these times are taking place today, but

78
00:04:56,310 --> 00:04:57,810
otherwise it has no idea.

79
00:04:58,440 --> 00:05:02,250
Same thing if I did something like so I can extract the year.

80
00:05:04,530 --> 00:05:09,540
But it's just going to give me 2022 because again, there's no date information here.

81
00:05:09,690 --> 00:05:11,280
It's only a time.

82
00:05:11,700 --> 00:05:17,250
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

83
00:05:17,250 --> 00:05:20,880
put to each date name and then birth date?

84
00:05:21,450 --> 00:05:27,390
Well, then I can do things like get the year from that birth date because there's a date portion,

85
00:05:27,390 --> 00:05:28,950
of course, in a date time.

86
00:05:29,600 --> 00:05:35,960
This has the date and the time, but I could also do things like get the month name.

87
00:05:37,340 --> 00:05:39,020
Of birth, date time.

88
00:05:39,740 --> 00:05:40,520
There we are.

89
00:05:40,520 --> 00:05:42,260
And I'm getting the month name.

90
00:05:42,290 --> 00:05:44,030
December, April, August, October.

91
00:05:44,030 --> 00:05:50,210
So both dates and date time, it probably goes without saying, are going to work with all these date

92
00:05:50,210 --> 00:05:56,630
functions year, day of the week, month, name, month, day of the year, week of the year.

93
00:05:56,660 --> 00:06:00,710
All that stuff that is specific to dates also works with date time.

94
00:06:00,950 --> 00:06:03,380
In the next video, we'll talk about time.