1 00:00:00,240 --> 00:00:01,560 All right, we're almost there. 2 00:00:01,589 --> 00:00:04,500 I've got a tiny bit more about formatting dates and times. 3 00:00:04,500 --> 00:00:09,810 I'm devoting one standalone video to a particular function called format date. 4 00:00:09,810 --> 00:00:15,280 But before we get there, here's a situation that it might help us with. 5 00:00:15,300 --> 00:00:24,300 Let's say that I want to for each person or people row in our database, I want to get a their birthday 6 00:00:24,300 --> 00:00:25,230 in this format. 7 00:00:25,230 --> 00:00:34,290 I want to see the month name like April, and then I want to have maybe a comma and then I want the 8 00:00:34,560 --> 00:00:35,340 day name. 9 00:00:35,340 --> 00:00:36,900 So let's just keep it even simpler. 10 00:00:36,900 --> 00:00:38,270 We don't need the comma. 11 00:00:38,280 --> 00:00:43,620 Let's just say I want it to be April and then 11th, 1985. 12 00:00:43,860 --> 00:00:47,430 I want it to be in this format instead of this format. 13 00:00:47,430 --> 00:00:49,020 So we could do this with what we know. 14 00:00:49,020 --> 00:00:50,570 We know how to get April, right? 15 00:00:50,580 --> 00:00:52,140 I would do month name. 16 00:00:52,140 --> 00:01:01,470 I mean, I have to select it, but select month, name of birth date and then comma, I could get the 17 00:01:01,470 --> 00:01:02,940 date or the day. 18 00:01:02,940 --> 00:01:10,650 So that's day of birth, date, comma, year of birth date from people. 19 00:01:10,830 --> 00:01:12,270 Let's make sure that part works. 20 00:01:12,270 --> 00:01:14,040 Now, it's not going to be one value. 21 00:01:14,070 --> 00:01:17,280 This will come back to me as three different columns. 22 00:01:17,280 --> 00:01:17,730 Right. 23 00:01:17,730 --> 00:01:20,550 But we have April 11, 1985. 24 00:01:21,000 --> 00:01:27,630 So then if I want to smush it together to form one date, well, I can concatenate it and it's going 25 00:01:27,630 --> 00:01:30,090 to be kind of nasty, can cat? 26 00:01:30,900 --> 00:01:32,280 All of this together. 27 00:01:32,280 --> 00:01:34,800 But of course now there's no spaces. 28 00:01:34,800 --> 00:01:41,460 So I need to add those empty spaces in there so I could come in here and add a space comma and then 29 00:01:41,460 --> 00:01:43,530 a space comma. 30 00:01:44,370 --> 00:01:45,870 And that will work. 31 00:01:47,720 --> 00:01:50,210 It's just a little clunky to do it that way. 32 00:01:50,630 --> 00:01:58,460 As you can see, it's a really long single call to con cat, and if I wanted to change things, I've 33 00:01:58,460 --> 00:02:00,080 got to move a lot of text around. 34 00:02:00,080 --> 00:02:02,150 I'm calling these functions nested in here. 35 00:02:02,150 --> 00:02:05,540 It's just not a great way of getting a longer formatted date. 36 00:02:05,540 --> 00:02:10,910 Or the same would go for time if I wanted to get the hour minute in second, but display them differently 37 00:02:10,910 --> 00:02:12,350 instead of this format. 38 00:02:12,350 --> 00:02:17,000 I could use Tomcat in all the different functions we've learned, but there's a better way. 39 00:02:17,000 --> 00:02:23,660 There is a built in function called date format and Dave Format is expecting us to pass through a very 40 00:02:23,660 --> 00:02:26,660 special string called a format string. 41 00:02:26,660 --> 00:02:32,810 And within this format string we have all these different specify or characters or specify our sequences 42 00:02:32,810 --> 00:02:33,950 that we can use. 43 00:02:34,430 --> 00:02:41,720 So I think the best way to do this is just to show you an example right here, we've got percent assigned 44 00:02:41,720 --> 00:02:45,980 B that means nothing to us or to me as a human. 45 00:02:45,980 --> 00:02:53,090 But in this format function, if I use percent B in a string, that will be replaced with the abbreviated 46 00:02:53,090 --> 00:02:55,460 month name for a given date. 47 00:02:55,580 --> 00:02:57,200 So let me show you that. 48 00:02:57,200 --> 00:02:59,510 Let's take the date format function. 49 00:02:59,690 --> 00:03:03,230 So I'll do select date format. 50 00:03:04,080 --> 00:03:10,110 And then within here, I have to pass through two things the date that I'm trying to format and then 51 00:03:10,110 --> 00:03:11,430 my format string. 52 00:03:11,430 --> 00:03:20,010 So let's format birth date, and then our format string will start with just that percent B sequence 53 00:03:20,010 --> 00:03:23,010 that I showed you from people. 54 00:03:23,010 --> 00:03:25,530 And let's also select the birth date. 55 00:03:26,870 --> 00:03:28,160 On its own first. 56 00:03:28,160 --> 00:03:35,990 So when I run it, this is what we see the abbreviated month, and that's because I used percent B if 57 00:03:35,990 --> 00:03:44,510 I tweaked that and I instead did percent C that gives me the month in a numeric format, like 12 or 58 00:03:44,510 --> 00:03:45,530 11 or ten. 59 00:03:46,610 --> 00:03:48,530 There's a whole bunch of other things in here. 60 00:03:48,530 --> 00:03:56,750 As you can see, I can get the full month name with a capital M, so percent capital M So why don't 61 00:03:56,750 --> 00:04:03,710 we do the abbreviated month name, but why don't we get the weekday first, which is nifty. 62 00:04:03,710 --> 00:04:11,720 We can do percent A to get the abbreviated weekday or to get the full weekday name somewhere down here 63 00:04:11,750 --> 00:04:19,040 percent Capital W So I don't remember any of these or almost any of these, but it doesn't matter. 64 00:04:19,040 --> 00:04:23,870 You come to the docs and if you know that you're trying to get some particular format, then you can 65 00:04:23,870 --> 00:04:26,600 find the corresponding sequence that you need to use. 66 00:04:26,750 --> 00:04:28,010 So let's try this. 67 00:04:28,010 --> 00:04:30,740 Let's do percent a first. 68 00:04:33,280 --> 00:04:37,360 And then I can put a space in there, a comma, a dash, whatever. 69 00:04:37,360 --> 00:04:38,800 I want to separate them. 70 00:04:38,800 --> 00:04:39,760 So I'll just do a space. 71 00:04:39,760 --> 00:04:42,760 For now, I need my semicolon. 72 00:04:42,760 --> 00:04:43,450 And there we go. 73 00:04:43,450 --> 00:04:46,540 We see Moon, December, blah, blah, blah. 74 00:04:47,350 --> 00:04:50,080 So why don't we keep going and get the actual date? 75 00:04:50,230 --> 00:04:56,230 If I want the day of the month, I can either get one that is padded with zeros, so like zero nine 76 00:04:56,230 --> 00:05:01,600 or just this version with percent E which doesn't have a leading zero. 77 00:05:02,230 --> 00:05:05,710 I can even get the day of the month with the English suffix. 78 00:05:06,550 --> 00:05:07,690 So that's kind of nifty. 79 00:05:07,930 --> 00:05:11,470 First, second, third, 15th, that sort of thing. 80 00:05:11,470 --> 00:05:13,570 Why don't we go for percent e. 81 00:05:15,220 --> 00:05:17,200 And I'll put a space there as well. 82 00:05:18,820 --> 00:05:20,530 And I keep forgetting my semicolon. 83 00:05:20,530 --> 00:05:21,160 And there we go. 84 00:05:21,160 --> 00:05:25,090 We get mun de sem dec das 25. 85 00:05:25,120 --> 00:05:30,970 If I change that to percent uppercase rd, we'll get that English suffix. 86 00:05:31,870 --> 00:05:32,410 We'll try that. 87 00:05:32,410 --> 00:05:36,100 Let me put my semicolon in so I don't keep having that same problem. 88 00:05:36,100 --> 00:05:38,950 And now we get 25th, 11th, 15th. 89 00:05:38,980 --> 00:05:43,090 All of them have t h at the end, which is sort of annoying. 90 00:05:43,090 --> 00:05:47,860 I don't have any first or second or third, but it is what it is. 91 00:05:47,950 --> 00:05:53,650 So I think that's probably enough examples working with date. 92 00:05:53,650 --> 00:05:58,240 But we also have different signifiers or specifiers. 93 00:05:58,240 --> 00:06:02,410 4 hours, 4 minutes, 4 seconds. 94 00:06:02,920 --> 00:06:04,570 You can see them all listed here. 95 00:06:05,320 --> 00:06:13,450 So percent SE is 4 seconds and we can combine them all together to get whatever formatted date or time 96 00:06:13,450 --> 00:06:14,350 that we want. 97 00:06:14,800 --> 00:06:21,280 There's also a if I go back here, a time format function, oops, come back. 98 00:06:22,580 --> 00:06:24,320 There is a time format. 99 00:06:24,350 --> 00:06:25,550 It's alphabetical. 100 00:06:25,580 --> 00:06:26,300 Where are you? 101 00:06:26,300 --> 00:06:27,020 Right here. 102 00:06:27,200 --> 00:06:34,460 And this works the same way, but it is only going to allow you to use the format specifiers that are 103 00:06:34,460 --> 00:06:36,230 valid for a time. 104 00:06:36,320 --> 00:06:41,390 So you can't use day of the week or month name or anything, but you can use hours, minutes, seconds 105 00:06:41,390 --> 00:06:42,050 and so on. 106 00:06:42,680 --> 00:06:49,100 Date format supports all of it, so I could format the date, time and extract. 107 00:06:49,100 --> 00:06:49,520 I don't know. 108 00:06:49,520 --> 00:06:54,020 How about the hours and minutes from my date time? 109 00:06:54,200 --> 00:06:55,220 Let's try that. 110 00:06:56,180 --> 00:06:58,400 Select Birth Date. 111 00:06:58,760 --> 00:06:59,570 Comma. 112 00:06:59,810 --> 00:07:01,400 Date Format. 113 00:07:01,970 --> 00:07:04,160 Birth Date for date time. 114 00:07:04,160 --> 00:07:08,710 I'm formatting the date times now and my string will have percent. 115 00:07:08,720 --> 00:07:12,560 I believe it's h 4 hours, but we can always double check. 116 00:07:13,490 --> 00:07:14,960 There's different variants. 117 00:07:15,080 --> 00:07:19,730 Do I want to start go from 0 to 23 or am I doing PM? 118 00:07:19,730 --> 00:07:21,010 Am pm time? 119 00:07:21,020 --> 00:07:21,370 Right. 120 00:07:21,380 --> 00:07:24,320 So 1 to 12, let's go with 24 hours. 121 00:07:24,320 --> 00:07:25,670 So Capital H. 122 00:07:26,810 --> 00:07:32,540 And then I want the minutes afterwards and that is I percent I. 123 00:07:32,570 --> 00:07:35,480 So we'll do percent I right there. 124 00:07:36,650 --> 00:07:37,280 OC. 125 00:07:37,950 --> 00:07:39,330 From people. 126 00:07:39,990 --> 00:07:41,310 We'll try running this one. 127 00:07:41,850 --> 00:07:47,520 And we see from each date time we got the hour and the minute and we put a colon in there, but we could 128 00:07:47,520 --> 00:07:52,620 put whatever we want and we can even add fancy things on like AM and PM. 129 00:07:52,620 --> 00:07:54,660 So if we wanted to do 12 hour time. 130 00:07:54,900 --> 00:07:55,920 Where is that? 131 00:07:55,950 --> 00:08:01,140 Well, there's actually a shortcut which is just percent ah is 12 hour time. 132 00:08:01,140 --> 00:08:02,210 Let's take a look at that. 133 00:08:02,220 --> 00:08:04,050 If I just replace this whole thing with percent. 134 00:08:04,050 --> 00:08:04,590 Ah. 135 00:08:06,160 --> 00:08:09,670 We get the full time, but with a AM and PM. 136 00:08:09,670 --> 00:08:12,280 But it is in 24 or in 12 hour time. 137 00:08:12,280 --> 00:08:15,760 So this 2359 became 11:59 p.m.. 138 00:08:16,000 --> 00:08:18,190 So that is the date format function. 139 00:08:18,190 --> 00:08:23,650 And again there's a time format which you can use, but you're restricted to the time. 140 00:08:23,650 --> 00:08:26,380 Different specifiers like minutes and seconds and hours. 141 00:08:26,380 --> 00:08:30,730 You can't use any of the date stuff, but either way they follow the same pattern. 142 00:08:30,730 --> 00:08:36,730 You provide the value or in our case, the column we're trying to format, and then the format string, 143 00:08:36,730 --> 00:08:41,380 which consists of one or more of these specifiers along with whatever we want. 144 00:08:41,380 --> 00:08:49,060 I mean, I can do something like Born on if I wanted to, and I would format it this way, and that 145 00:08:49,060 --> 00:08:51,220 is my resulting formatted string. 146 00:08:51,280 --> 00:08:58,090 Each one of these special signifiers will be replaced with their corresponding piece of information 147 00:08:58,090 --> 00:09:01,180 that has been extracted out, and it's really powerful. 148 00:09:01,180 --> 00:09:06,940 We can get, as we saw the English suffix day of the month, or we can get the date of the week or the 149 00:09:06,940 --> 00:09:12,040 abbreviated day of the week, the abbreviated month name, the full month name and a lot of information. 150 00:09:12,130 --> 00:09:13,630 So that is date format.