1 00:00:00,000 --> 00:00:05,580 this is part 125 of sequel server 2 00:00:02,190 --> 00:00:08,099 tutorial in this video well discuss evo 3 00:00:05,580 --> 00:00:10,800 month function in sequel server Evo 4 00:00:08,099 --> 00:00:12,900 stands for and off so this is end of 5 00:00:10,800 --> 00:00:15,450 month function it's introduced in 6 00:00:12,900 --> 00:00:17,430 sequence over 2012 it returns the last 7 00:00:15,450 --> 00:00:19,590 day of the month of the specified date 8 00:00:17,430 --> 00:00:21,448 here is the syntax the first parameter 9 00:00:19,590 --> 00:00:23,640 for this function is the start date so 10 00:00:21,449 --> 00:00:25,949 that is the date for which to return the 11 00:00:23,640 --> 00:00:27,960 last day of the month the second 12 00:00:25,949 --> 00:00:30,539 parameter is month to add this is 13 00:00:27,960 --> 00:00:32,820 optional this parameter specifies the 14 00:00:30,539 --> 00:00:35,370 number of months to add to the start 15 00:00:32,820 --> 00:00:37,500 date so if we specify a value for the 16 00:00:35,370 --> 00:00:39,269 second optional parameter then end of 17 00:00:37,500 --> 00:00:41,820 month is going to add those number of 18 00:00:39,270 --> 00:00:44,219 months to the start date and then it is 19 00:00:41,820 --> 00:00:46,530 going to return the last day from the 20 00:00:44,219 --> 00:00:49,200 resulting date let's look at an example 21 00:00:46,530 --> 00:00:51,899 now so notice here we are passing this 22 00:00:49,200 --> 00:00:53,039 state to end of month function and if 23 00:00:51,899 --> 00:00:56,610 you look at the month here it is 24 00:00:53,039 --> 00:00:59,760 November and November month has got 30 25 00:00:56,610 --> 00:01:04,289 days so the last day of November 20th 26 00:00:59,760 --> 00:01:06,298 2015 should be 30th of November 2015 I 27 00:01:04,290 --> 00:01:08,670 have the same query already typed here 28 00:01:06,299 --> 00:01:13,380 so when we execute this we should get 29 00:01:08,670 --> 00:01:16,140 30th of November as the last day now 30 00:01:13,380 --> 00:01:18,390 let's look at an example of retrieving 31 00:01:16,140 --> 00:01:22,229 the last day from the February month 32 00:01:18,390 --> 00:01:25,080 from a non-leap year 2015 is a non-leap 33 00:01:22,229 --> 00:01:27,390 year and we want the last day of 34 00:01:25,080 --> 00:01:30,600 February month so I'm going to specify 35 00:01:27,390 --> 00:01:33,960 the month number as 2 in a non-leap year 36 00:01:30,600 --> 00:01:36,149 February has 28 days so when we execute 37 00:01:33,960 --> 00:01:39,750 this query we should get 20th of 38 00:01:36,150 --> 00:01:42,450 February as the last day notice that we 39 00:01:39,750 --> 00:01:44,610 get 28th of February as the last day now 40 00:01:42,450 --> 00:01:48,540 let's look at doing the same thing from 41 00:01:44,610 --> 00:01:50,970 alipio 2016 is alipio now how do we know 42 00:01:48,540 --> 00:01:54,270 a specific year is in leap year if that 43 00:01:50,970 --> 00:01:57,960 year is divisible by 4 then we know it's 44 00:01:54,270 --> 00:02:01,320 a leap year so 2016 is divisible by 4 so 45 00:01:57,960 --> 00:02:04,770 we get 504 so that's alipio with an 46 00:02:01,320 --> 00:02:07,589 olympia february has got 29 days so when 47 00:02:04,770 --> 00:02:11,790 we execute the same query we should get 48 00:02:07,590 --> 00:02:12,970 29th of february as the last day notice 49 00:02:11,790 --> 00:02:15,489 29th of 50 00:02:12,970 --> 00:02:17,410 ready now let's look at an example of 51 00:02:15,490 --> 00:02:20,470 using the second optional parameter 52 00:02:17,410 --> 00:02:22,840 month to add when we specify a value for 53 00:02:20,470 --> 00:02:25,330 this parameter it's going to add those 54 00:02:22,840 --> 00:02:28,270 many number of months to the start date 55 00:02:25,330 --> 00:02:29,770 and from the resulting date the end of 56 00:02:28,270 --> 00:02:31,960 month is going to return us the last 57 00:02:29,770 --> 00:02:37,000 eight so the moment the day that we have 58 00:02:31,960 --> 00:02:40,270 is February 28 2016 now what I'm going 59 00:02:37,000 --> 00:02:42,640 to do is add two months to it so when we 60 00:02:40,270 --> 00:02:46,120 add two months to February we will get 61 00:02:42,640 --> 00:02:51,369 April March and April okay so we want to 62 00:02:46,120 --> 00:02:54,790 find the last day of April 2016 so let's 63 00:02:51,370 --> 00:02:58,890 execute this in April we've got 30 days 64 00:02:54,790 --> 00:03:01,120 look at that April 30th is the last day 65 00:02:58,890 --> 00:03:03,880 now let's look at an example of 66 00:03:01,120 --> 00:03:06,160 subtracting a month you know from the 67 00:03:03,880 --> 00:03:08,350 start date to subtract a month all you 68 00:03:06,160 --> 00:03:11,320 have to do is use a negative number 69 00:03:08,350 --> 00:03:13,720 let's say I want to subtract one month 70 00:03:11,320 --> 00:03:15,730 and then compute the end of that 71 00:03:13,720 --> 00:03:17,680 resulting date so in this case it's 72 00:03:15,730 --> 00:03:20,018 going to subtract one month so when we 73 00:03:17,680 --> 00:03:22,930 subtract one from February we are going 74 00:03:20,019 --> 00:03:25,180 to get you know January 2016 in the 75 00:03:22,930 --> 00:03:29,560 month of January we have caught 31 days 76 00:03:25,180 --> 00:03:33,580 so January 31st 2016 should be the last 77 00:03:29,560 --> 00:03:36,070 day we have all those examples right 78 00:03:33,580 --> 00:03:38,620 here now let's look at an example of 79 00:03:36,070 --> 00:03:41,709 using this end of month function with 80 00:03:38,620 --> 00:03:44,110 table data we'll use this employees 81 00:03:41,709 --> 00:03:45,940 table for this example notice that we've 82 00:03:44,110 --> 00:03:48,580 got three columns in this table ID name 83 00:03:45,940 --> 00:03:52,450 and date of birth what we want to do is 84 00:03:48,580 --> 00:03:54,790 return the last day of the month from 85 00:03:52,450 --> 00:03:57,329 the date of birth of every employee so 86 00:03:54,790 --> 00:04:02,200 if you look at mark is born on you know 87 00:03:57,330 --> 00:04:05,080 January 11th 1980 Don is born on 12th of 88 00:04:02,200 --> 00:04:08,109 December 1981 so from their date of 89 00:04:05,080 --> 00:04:12,280 births we want the last day of the month 90 00:04:08,110 --> 00:04:15,610 so for January it is 31st of January for 91 00:04:12,280 --> 00:04:18,459 December this 31st of December for 92 00:04:15,610 --> 00:04:20,890 November it is 30th of November so from 93 00:04:18,459 --> 00:04:21,690 the date of birth of every employee we 94 00:04:20,890 --> 00:04:24,900 want the 95 00:04:21,690 --> 00:04:27,980 last day of that month okay so let's 96 00:04:24,900 --> 00:04:31,650 look at an example of using that 97 00:04:27,980 --> 00:04:35,190 end-of-month function with table data so 98 00:04:31,650 --> 00:04:39,359 I'm going to use end of month and we 99 00:04:35,190 --> 00:04:41,610 want to use it on date of birth column 100 00:04:39,360 --> 00:04:48,420 and what I'm going to do is retrieve the 101 00:04:41,610 --> 00:04:51,510 date of birth column as well and let's 102 00:04:48,420 --> 00:04:55,740 give this column the last column and 103 00:04:51,510 --> 00:04:58,110 aleeah's let's call this last day so 104 00:04:55,740 --> 00:05:00,900 when we execute this we should get the 105 00:04:58,110 --> 00:05:03,630 last day from the date of birth of every 106 00:05:00,900 --> 00:05:05,219 employee okay so at the moment we are 107 00:05:03,630 --> 00:05:07,740 getting the complete date 108 00:05:05,220 --> 00:05:10,800 now instead of complete date if you want 109 00:05:07,740 --> 00:05:13,230 just the last day as you can see here 110 00:05:10,800 --> 00:05:16,290 from the month of their date of birth 111 00:05:13,230 --> 00:05:19,350 then use date part function so all you 112 00:05:16,290 --> 00:05:21,270 need to do here is so this end of month 113 00:05:19,350 --> 00:05:23,970 is actually going to return a date so 114 00:05:21,270 --> 00:05:25,950 from that date I want a part of it so 115 00:05:23,970 --> 00:05:29,160 I'm going to use date part function all 116 00:05:25,950 --> 00:05:34,140 I want is the date part okay I don't 117 00:05:29,160 --> 00:05:36,480 want month and year so we want date part 118 00:05:34,140 --> 00:05:38,729 when we execute this we should get only 119 00:05:36,480 --> 00:05:42,110 the last day of the month not the 120 00:05:38,730 --> 00:05:44,160 complete data as you can see it here 121 00:05:42,110 --> 00:05:46,610 thank you for listening and have a great 122 00:05:44,160 --> 00:05:46,610 day