WEBVTT 00:00.150 --> 00:00.840 All right. 00:00.870 --> 00:01.710 Welcome back. 00:01.710 --> 00:08.340 So in this video we're continuing with dates not the most exciting topic potentially I know but an important 00:08.340 --> 00:09.320 one nonetheless. 00:09.510 --> 00:11.420 So we're talking about formatting days. 00:11.460 --> 00:18.990 Right now we have dates times date times all stored in a somewhat clunky condensed but difficult to 00:18.990 --> 00:20.720 read at least for human. 00:20.780 --> 00:23.100 It's difficult to read format just not very nice. 00:23.190 --> 00:26.490 So how can we extract things out of let's say a date time. 00:26.580 --> 00:30.770 How can I convert that so that it says something like. 00:31.170 --> 00:32.740 April 21st. 00:32.880 --> 00:41.220 Like the full month name followed by a space Twenty-First to 1 S T and then the full year rather than 00:41.540 --> 00:45.890 you know 2017 dash 0 4 dash 2 2. 00:46.230 --> 00:51.780 So we're going to do that first thing I want to show you though is that in the documentation there is 00:51.780 --> 00:55.030 a pretty massive list of date and time functions. 00:55.300 --> 00:58.990 And I will be honest with you. 00:59.040 --> 01:05.280 Most of these I never use and you likely will never use that won't stop me from showing some of them 01:05.280 --> 01:10.590 to you and I'm going to try and highlight which ones are most important and the first two I'm going 01:10.590 --> 01:11.120 to show you. 01:11.160 --> 01:17.250 I don't actually think are that important but they are nice to know that they exist and they're a set 01:17.250 --> 01:19.160 of these one off little functions. 01:19.170 --> 01:21.100 I'm not sure even what to call them. 01:21.330 --> 01:27.480 But basically there's a group of them at least in my mind that grouped together things that will extract 01:27.480 --> 01:36.600 an individual piece of information like day or day name or day of month day or week day of year hour 01:37.350 --> 01:38.930 month minute. 01:39.420 --> 01:40.870 So let me show you what I mean. 01:41.220 --> 01:45.030 So I'm going to hop over to cloud 9 and we have a select star. 01:45.120 --> 01:56.930 Let's only worked with birth date for now so we'll select name comet birth date from. 01:57.250 --> 01:57.720 OK. 01:57.940 --> 02:05.120 So instead of displaying the date here or the entire date what I can do is use some of those things 02:05.120 --> 02:05.930 that we talked about. 02:06.010 --> 02:10.700 Whatever that group is trying to day you'll see what it does. 02:10.720 --> 02:20.820 So rather than birth date I'll select day of birth date and all that it does is extract the day. 02:21.170 --> 02:25.930 So for you compare actually what might be better. 02:26.320 --> 02:35.600 And if I keep a birth date and I just add in an extra column with day so it's extracting the day 11 02:35.840 --> 02:38.420 25 and 21. 02:38.420 --> 02:40.500 So there's other things we can do. 02:40.910 --> 02:45.910 Let's just go with this list to have a peer day name. 02:46.080 --> 02:47.520 And this is a bit more useful. 02:47.520 --> 02:51.530 There are times when you want to do this as you can see we're extracting that. 02:51.600 --> 02:53.380 So turns out this is a Friday. 02:53.380 --> 02:54.490 This is Saturday. 02:54.550 --> 02:56.760 This is another Friday. 02:56.850 --> 02:58.360 I know some people can figure this out. 02:58.360 --> 03:00.960 Mentally there's a whole trick to doing it. 03:00.960 --> 03:07.700 I'm not one of those people so we just have to trust it that this is indeed a Friday. 03:07.830 --> 03:09.230 Moving right along. 03:09.630 --> 03:10.110 We can try. 03:10.110 --> 03:11.240 Day of week. 03:11.730 --> 03:14.500 And you might wonder how is that different. 03:15.450 --> 03:17.280 Well it gives you a number. 03:17.280 --> 03:22.610 So Friday is a six Saturdays seven Sundays one and so on. 03:23.200 --> 03:28.940 Then we also have day of year and that will tell us starting from the beginning of the year. 03:28.940 --> 03:32.440 How many days was at Intel. 03:32.970 --> 03:34.330 Misstepped the parentheses. 03:36.770 --> 03:39.230 Area until that date. 03:39.230 --> 03:48.160 So for something on Christmas of 1943 it was that 350 the day of the year my cat blue. 03:48.160 --> 03:51.250 Come on Blue. 03:51.590 --> 03:54.370 And this of course takes into account things like leap years. 03:54.560 --> 03:56.930 So important to know. 03:57.560 --> 04:01.530 With that said these also applied to date times. 04:01.670 --> 04:06.830 So if you tried to do it on time we start right there instead of doing first date. 04:07.130 --> 04:13.280 And you tried to do first time while others never be an immediate problem which is we're not storing 04:13.610 --> 04:16.860 day of the year or day information at all. 04:17.850 --> 04:22.410 But just to show you we don't get an error right. 04:22.430 --> 04:25.500 We get warnings but we don't get something that breaks everything. 04:25.610 --> 04:27.910 We just get no it doesn't work. 04:28.580 --> 04:35.150 But we can do it on a date time and that's what Like I said I use most of the time. 04:35.150 --> 04:43.000 So if we do birth D.T. Instead you can see that it's giving us the correct numbers here. 04:43.120 --> 04:46.060 3:59 right 11 and so on. 04:46.100 --> 04:53.840 So we we can use all of ones I've seen Day Day name but there are other things too briefly that work 04:53.840 --> 04:55.700 for date and date time. 04:55.700 --> 04:59.230 So things like month. 04:59.300 --> 05:01.480 So that tells us the month. 05:01.480 --> 05:02.600 What else is there. 05:02.830 --> 05:05.460 I think there's a month name yet. 05:06.160 --> 05:10.500 So if you want the full name of the month. 05:10.540 --> 05:11.550 There we go. 05:12.070 --> 05:18.610 And then lastly when we're working with the time so if we go and changes to birth time 05:23.000 --> 05:25.510 there are a few methods that apply here. 05:25.520 --> 05:35.860 We can do things like our if you want to extract the hour or minute. 05:35.860 --> 05:42.330 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 05:42.340 --> 05:46.080 second on their own effort really. 05:46.090 --> 05:48.560 So that's part one of formatting dates. 05:48.610 --> 05:53.540 Imagine if you wanted to use something like this instead of just printing out the day or something. 05:53.710 --> 06:08.350 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 06:08.350 --> 06:10.080 21st 2017. 06:10.510 --> 06:13.720 So how would we take this date. 06:13.780 --> 06:19.260 That would be 2017 dash 0 4 dash 21 and convert it to this. 06:19.430 --> 06:24.130 Well we could use what we've already seen and do a giant Concat. 06:24.280 --> 06:29.230 I won't make you do this entire thing but basically we could extract the month name 06:32.600 --> 06:38.780 get this and then concatenate it out with a space and then this part's a little trickier right. 06:38.790 --> 06:40.270 You need to. 06:40.280 --> 06:41.250 It's going to be a pain. 06:41.260 --> 06:46.960 Honestly you would have to have some magic that I haven't shown you how to do yet because you don't 06:46.960 --> 06:49.520 always add as T after a number. 06:49.910 --> 06:52.480 What if it's the 23rd. 06:52.480 --> 06:56.090 So that's a little bit more complicated so let's reduce it to this. 06:56.110 --> 06:59.530 So for that we would want to get the day right. 06:59.770 --> 07:02.060 And then we want that year. 07:03.010 --> 07:05.240 So you could do a giant Concat. 07:05.380 --> 07:09.250 Basically it would take all of these birth date. 07:09.780 --> 07:11.210 It's just going to get messy. 07:11.410 --> 07:13.440 And the point is that there's a better way. 07:13.720 --> 07:15.280 But imagine you're doing this. 07:15.340 --> 07:23.200 So we have a concat month name of the birth date comma with a space comma the day of the birth date 07:24.010 --> 07:32.030 comma another space comma that year or if the birth date is a bit of a mess. 07:32.470 --> 07:39.670 But let's see if it does work so we'll just select that from people. 07:39.700 --> 07:40.920 There we go. 07:40.930 --> 07:49.250 November 11 1993 December 25th 1943 25 1943 April 21 2017. 07:49.600 --> 07:50.560 So it does work. 07:50.800 --> 07:53.120 But this is not a great way of formatting dates. 07:53.140 --> 07:58.630 And in fact there's a much better way which you probably gathered because the way that I set this up 07:59.070 --> 08:06.240 there is an entirely separate method or function excuse me that we can use called date format. 08:06.700 --> 08:09.280 So date format is really really useful in my opinion. 08:09.280 --> 08:11.050 I use it all the time. 08:11.050 --> 08:17.650 What it does it looks a little intimidating looking at the documentation but what it does is it allows 08:17.650 --> 08:20.650 us to format things nicely and powerfully. 08:20.650 --> 08:22.940 We have a lot of different things that we can do. 08:23.290 --> 08:28.810 So these are called specifiers over here there's a table of them and they're basically little symbols 08:28.810 --> 08:33.890 little shortcuts that allow us to specify what we want from that date. 08:34.060 --> 08:41.080 So let's say we're working with the date time and if I want to get the month name I use percent sign 08:41.290 --> 08:50.950 capital M. and if I want the month number then I use percents on lowercase m if I wanted to get the 08:50.950 --> 08:57.210 day of the week as the weekday name I use percent upper uppercase w if I want the date that week has 08:57.240 --> 09:00.500 number is percent lower case w and so on. 09:01.840 --> 09:06.870 So what we do is combine them together in a string. 09:07.300 --> 09:13.270 And when you pass it into date format it will take a date and then the string of how you want to format 09:13.270 --> 09:16.170 it and spit out a nice answer for you. 09:16.180 --> 09:18.030 So here's an example. 09:18.130 --> 09:22.630 Use my fridge there my ice maker I guess being noisy. 09:22.660 --> 09:26.390 So we've got date format of this date time here. 09:26.740 --> 09:29.070 It works some day times and just plain days. 09:29.290 --> 09:35.650 So here's the date time though my fridge making so much noise. 09:36.150 --> 09:37.000 I think it's done. 09:38.330 --> 09:43.340 So we have this date time and we're formatting it with this which looks a little bit like jibberish 09:43.730 --> 09:50.760 but we have percent uppercase W. which gives us the day of the week as the word the name of the day 09:50.760 --> 09:51.400 of the week. 09:51.560 --> 09:58.040 And then a space which gives us that space then we have present upper case M which is the name of the 09:58.040 --> 09:58.910 month. 09:59.060 --> 10:05.480 So we have October and then another space and then we have percent y which gives us the year. 10:06.080 --> 10:08.630 So really useful and versatile. 10:08.630 --> 10:10.800 I'm just going to just copy this one over. 10:11.000 --> 10:13.670 Just run it so that you can see what happens. 10:16.380 --> 10:23.630 If I hit Enter we get Sunday October 2009 but we're free to put whatever we want in here so we can do 10:24.430 --> 10:25.480 dashes. 10:25.640 --> 10:31.730 Not that it really makes sense but if we put dashes there it still puts important information here that 10:31.730 --> 10:32.870 we specified. 10:33.110 --> 10:35.080 But now they're separated by dashes. 10:35.630 --> 10:40.940 So it gives us some freedom we can do you know group them together like that if we wanted to put commas 10:41.330 --> 10:46.440 or we can put an entire word before after we can do whatever we want. 10:46.880 --> 10:53.370 So this is really really useful if we go back and select let's work with date time. 10:53.450 --> 10:56.980 So we'll do select date format 10:59.870 --> 11:07.600 and then we're working with birth T and then some string that will provide in just a second. 11:07.640 --> 11:08.540 We'll come back to that 11:11.630 --> 11:21.340 from people we can start with something really simple like just getting the day of the week has a yellow 11:21.340 --> 11:24.610 to do day of the week first as a word so uppercase w 11:27.850 --> 11:36.940 we could do something like was born on a Friday Saturday or Friday. 11:38.370 --> 11:42.840 But as you saw there are a bunch of other things that we can do here. 11:43.270 --> 11:46.100 Know we can work with hours and minutes. 11:46.350 --> 11:51.610 We can work with second month names tons and tons of things. 11:51.690 --> 11:54.180 It's really versatile and very useful. 11:54.630 --> 11:59.610 And typically the things that I end up doing with it are just formatting data so that they look like 12:01.160 --> 12:10.320 you know at least in the US where we put the month first followed by the day followed by the year. 12:10.570 --> 12:11.800 So that's pretty simple to do. 12:11.800 --> 12:13.370 Think about how you would do it though. 12:13.540 --> 12:21.560 If you want to give it a shot how do we take this and change it into that so we can do that now. 12:22.690 --> 12:26.700 All that we want is good month number. 12:26.740 --> 12:27.310 Right. 12:27.340 --> 12:37.540 So that's percent Am I believe followed by a slash and the day present D followed by a year which is 12:37.560 --> 12:40.870 percent why and see if that works. 12:41.400 --> 12:46.420 Almost If you notice I use lower case Y and that gives us that two. 12:46.920 --> 12:51.470 I don't know we call this the abbreviated year the last Dugit digits of the year. 12:51.660 --> 12:55.580 So we want uppercase Y and I just have to remember that. 12:55.710 --> 13:02.200 But if you couldn't remember how to do it you can solve this and you'd find it. 13:02.210 --> 13:06.000 Where's that your numeric 4 digits versus two digits. 13:06.190 --> 13:10.700 So let's wrap all this up we talked about a lot of different ways of formatting dates. 13:11.230 --> 13:13.600 I frankly don't use these that often. 13:13.600 --> 13:18.640 Like I said because you can do the same thing using date format. 13:18.640 --> 13:20.790 I should mention there's also a time format. 13:20.800 --> 13:23.410 It works exactly the same way. 13:23.560 --> 13:27.940 You just pass in different or you pass in a different format or here. 13:27.940 --> 13:33.760 So you're not going to be able to get month day and year from a time that you can get you know hours 13:33.760 --> 13:34.980 minutes seconds. 13:35.530 --> 13:41.140 But like I said I'm not working with times as often and if I am using a time component within a date 13:41.140 --> 13:43.570 time so I might do something like this 13:47.270 --> 13:59.830 at let's say let's just do hour and minute and we'll put a call in there and there you go. 13:59.880 --> 14:02.190 So I'll do something like that. 14:02.490 --> 14:08.140 But I'm rarely working with just a time but there is a time format if you do need to do that. 14:08.610 --> 14:08.850 OK. 14:08.850 --> 14:11.210 So back to where I was wrapping up here. 14:11.340 --> 14:13.540 There are all these weird one off functions. 14:13.560 --> 14:17.910 I don't use them though because you can just replicate it using tape format and it's much easier for 14:17.910 --> 14:23.710 me just to remember one function date format rather than 15 separate functions. 14:23.790 --> 14:26.180 So that's the basic formatting dates. 14:26.190 --> 14:28.800 Next up we're going to talk a bit about doing math with Dade's.