1 00:01.040 --> 00:01.910 So. 2 00:03.230 --> 00:04.010 Hi everyone. 3 00:04.010 --> 00:08.180 We have seen up to here uh, yesterday in our class. 4 00:08.690 --> 00:16.160 So today we will see from the date different function which is also a part of the date function. 5 00:16.790 --> 00:21.590 So now in the day what will this function do. 6 00:21.830 --> 00:25.070 Used to find the difference between two dates. 7 00:25.580 --> 00:30.670 So we have one date will be there and we have another date will be there. 8 00:31.360 --> 00:38.020 And using this date diff function, it will give us the difference between two dates, either in year 9 00:38.020 --> 00:43.960 or in month, or in quarter or in days, whichever we are passing. 10 00:44.770 --> 00:51.610 So if you see over here this is the syntax dates date diff then date part. 11 00:51.760 --> 00:56.990 Over here we are giving the date part which means in which format. 12 00:56.990 --> 01:00.350 We want the difference between this start date and end date. 13 01:00.950 --> 01:02.060 So it will be. 14 01:02.330 --> 01:11.330 It returns an integer and either in year or in month, or in quarter only or in days. 15 01:12.290 --> 01:15.350 We want difference, so it will give us the difference. 16 01:16.100 --> 01:17.530 Now let's see. 17 01:21.220 --> 01:28.840 Now if we pass this particular date like 2022 1st January 2022. 18 01:29.470 --> 01:35.980 And over here we are giving some 202 3rd February zero six. 19 01:36.490 --> 01:38.290 So it will give us the difference. 20 01:38.290 --> 01:41.490 And we are passing over here in the date diff function here. 21 01:42.120 --> 01:44.730 So it will see only this one. 22 01:45.120 --> 01:53.400 It will not see, uh, go to the date and go to the month, month and date. 23 01:54.420 --> 01:57.990 It will only see the year from this particular date and it. 24 01:58.020 --> 01:59.910 So over here 2022. 25 01:59.910 --> 02:01.350 And over here 2023. 26 02:01.350 --> 02:02.250 What is the difference. 27 02:02.250 --> 02:03.240 It is one year. 28 02:03.330 --> 02:06.430 So it will give me one year as a difference. 29 02:07.840 --> 02:09.880 So let me execute this query. 30 02:09.940 --> 02:15.460 So it is giving me one year as a difference right now. 31 02:15.460 --> 02:16.600 Let's move ahead. 32 02:17.170 --> 02:24.100 Now if I want the same same difference but in month this time not in year. 33 02:24.190 --> 02:34.410 So in place of year I have to pass over year month only then all second and third argument will be same. 34 02:34.410 --> 02:37.980 So date this function will accept three arguments. 35 02:37.980 --> 02:40.440 So now let me execute this query. 36 02:40.920 --> 02:42.570 It will give me 13. 37 02:43.410 --> 02:44.640 So one. 38 02:47.130 --> 02:48.450 12 of this. 39 02:50.730 --> 02:54.020 11 of this month and one of this month. 40 02:55.160 --> 02:55.730 Sorry. 41 02:55.730 --> 02:56.510 This year. 42 03:02.720 --> 03:06.500 Or we can say, uh, trail of this trail of this year and one of. 43 03:09.290 --> 03:10.370 This month. 44 03:11.150 --> 03:11.690 Sorry. 45 03:11.690 --> 03:12.410 This year. 46 03:12.410 --> 03:18.420 So it is giving me 13 as a in month 13 difference between the two. 47 03:18.450 --> 03:21.510 These two particular days in months. 48 03:21.810 --> 03:30.750 Now if I want the difference in quarter, then again this will be giving me four as a quarter for quarter. 49 03:30.840 --> 03:36.420 So three months there is a three month one quarter, then another three month, one quarter, another 50 03:36.420 --> 03:37.800 three month, one quarter. 51 03:37.860 --> 03:43.190 But in this particular case, this quarter is not yet completed, right. 52 03:43.670 --> 03:49.430 So if I mention over here for ten zero one, then let me try. 53 03:51.590 --> 03:52.940 So it will give me five. 54 03:52.940 --> 03:53.330 Right. 55 03:53.330 --> 03:56.030 So now this quarter is completed. 56 03:56.510 --> 04:00.110 First quarter of 2023 is completed. 57 04:00.110 --> 04:02.900 So it is giving me five this time. 58 04:04.310 --> 04:05.980 Now let's move ahead. 59 04:06.010 --> 04:14.800 If I want the difference between two dates into the days, like 80 days, 20 days, 200 days, 300 days, 60 04:15.340 --> 04:23.560 then I have just changing my first argument from year, month and quarter. 61 04:23.590 --> 04:26.440 Now this time I am passing day. 62 04:28.270 --> 04:32.840 So it will give me 401 this to date. 63 04:32.870 --> 04:36.710 Difference between these two date in days is 401. 64 04:39.710 --> 04:41.510 Now important thing. 65 04:41.510 --> 04:42.950 Observe the following. 66 04:47.360 --> 04:48.620 Now over here. 67 04:48.620 --> 04:58.700 If you see the difference between these two, particular date is it is December 2020 and it is January 68 04:58.700 --> 05:00.070 2nd 021. 69 05:00.730 --> 05:02.170 So it is not. 70 05:05.050 --> 05:10.600 It is not even one month of difference, right? 71 05:10.600 --> 05:13.060 It is not even one month of difference. 72 05:14.560 --> 05:17.590 But still it is giving me one year. 73 05:17.610 --> 05:18.360 Why? 74 05:18.990 --> 05:22.260 Because the year function will check only. 75 05:25.410 --> 05:27.960 This argument and this argument. 76 05:31.410 --> 05:34.680 So 2021 -2020. 77 05:34.680 --> 05:36.330 So it will give me one year. 78 05:36.330 --> 05:41.070 So it will not check any other thing in this particular case. 79 05:41.070 --> 05:43.840 So it will give me one year as a difference. 80 05:44.140 --> 05:45.520 Now check this. 81 05:45.760 --> 05:46.810 Read this. 82 05:47.320 --> 05:54.220 Notice that the given function will return one year, even though difference between two dates is not 83 05:54.220 --> 05:55.360 even one month. 84 05:56.140 --> 06:03.100 It is returning is returning one year as year part has changed from 2020 to 2021. 85 06:03.100 --> 06:07.170 Hence difference is terms of year is one year. 86 06:08.610 --> 06:10.470 Now again check this one. 87 06:12.300 --> 06:14.610 So this is same year. 88 06:15.330 --> 06:16.890 This is same year. 89 06:17.130 --> 06:19.890 But over here it is 11. 90 06:19.890 --> 06:21.720 And over here it is 12. 91 06:22.410 --> 06:27.210 And if you see the entire difference then it will be around four days only. 92 06:27.630 --> 06:29.700 It will be around four days only. 93 06:29.900 --> 06:32.270 But still it will give me one month. 94 06:32.300 --> 06:33.020 Why? 95 06:33.470 --> 06:38.000 Because this time it will see 11 and it will see 12. 96 06:38.000 --> 06:40.610 So what is the difference between 11 and 12? 97 06:40.970 --> 06:42.950 It is one month, right? 98 06:43.130 --> 06:45.470 So it will give me one month. 99 06:49.040 --> 06:50.180 As difference. 100 06:52.820 --> 06:58.770 Again the difference between two days is barely four days, but function will return one month as month. 101 06:58.770 --> 07:03.720 Part has changed from 11 to 12 11 to 12. 102 07:03.750 --> 07:08.040 Hence difference in terms of month will be one. 103 07:09.480 --> 07:11.010 Now let's move ahead. 104 07:30.710 --> 07:32.000 Now say. 105 07:38.300 --> 07:44.050 Display each employee details and their experience in here till date from MP. 106 07:44.770 --> 07:53.080 So now what we want display each employee details means employee name, hire date or any detail in their 107 07:53.080 --> 07:55.840 experience in years till date. 108 07:55.870 --> 07:58.210 Till date means today's date. 109 07:58.210 --> 08:01.000 So how can we get the till date? 110 08:01.090 --> 08:07.920 So till date equal to today's date equal to. 111 08:07.920 --> 08:10.260 We can get it through get date right. 112 08:11.820 --> 08:13.440 We can get it through get date. 113 08:13.440 --> 08:16.470 So now over here we need to identify. 114 08:16.500 --> 08:19.410 We need to check what is the higher date. 115 08:19.860 --> 08:23.700 What is the higher date that will come first. 116 08:23.790 --> 08:26.520 Then what is the get date means today's date. 117 08:27.090 --> 08:31.930 And now I want the difference between these two particular date in two year. 118 08:34.000 --> 08:35.080 In two year. 119 08:35.350 --> 08:38.200 So it will give me MP name. 120 08:38.350 --> 08:42.580 It will give me the difference in year between higher date and get date. 121 08:42.670 --> 08:44.110 So now let's see. 122 08:47.260 --> 08:52.420 So if you see over here this is 2024. 123 08:52.420 --> 08:54.270 And this is again 2024. 124 08:54.270 --> 08:59.790 So zero zero years now this is 1983 and 2024. 125 08:59.910 --> 09:01.890 So it will give me 41 years. 126 09:01.890 --> 09:05.220 And this is 1981 and 2024. 127 09:05.220 --> 09:07.410 So it is giving 43 years. 128 09:07.830 --> 09:13.770 So this particular employee is working in an organization from 43 years. 129 09:15.900 --> 09:25.520 We can say that this particular employee is working in the organization till from till 43 years right 130 09:26.900 --> 09:27.200 now. 131 09:27.200 --> 09:28.760 What is our requirement? 132 09:33.200 --> 09:37.970 Now we want to display each employee details and their experience in years. 133 09:40.130 --> 09:42.870 And month till date From MP. 134 09:45.870 --> 09:47.430 Till date from MP. 135 09:47.490 --> 09:51.930 So let's see now what we have done over here. 136 09:51.930 --> 09:54.960 We have passing this thing as it is. 137 09:55.350 --> 09:58.530 But now we want the difference in months as well. 138 09:58.530 --> 10:04.530 So what we have done, we are passing the parameter first parameter as month. 139 10:06.980 --> 10:13.100 We have passing the first parameter as month in her date and get date will remain the same. 140 10:13.100 --> 10:22.310 So this query will give me the difference in year as well as well as month. 141 10:22.520 --> 10:23.450 Both right? 142 10:28.010 --> 10:30.140 From Pi 524 months. 143 10:30.140 --> 10:34.090 This particular employee is working in an organization. 144 10:36.100 --> 10:37.780 We can say like that. 145 10:41.410 --> 10:43.540 Now let's see one more function. 146 10:43.540 --> 10:44.920 Date add function. 147 10:45.790 --> 10:54.160 So it adds a number of date part or input date and returns modified date oblique date time. 148 10:56.270 --> 10:57.170 So date. 149 10:57.560 --> 10:58.520 Dated function. 150 10:58.520 --> 11:00.230 What it will do. 151 11:01.070 --> 11:02.210 Same thing. 152 11:02.210 --> 11:04.700 We have to pass three parameters. 153 11:06.830 --> 11:09.350 This time we have to pass three parameters. 154 11:09.800 --> 11:12.800 First parameter is date part. 155 11:13.370 --> 11:17.390 What kind of date part you want to add in a particular date? 156 11:17.830 --> 11:23.650 like you want to add a day, or you want to add a month, or you want to add year? 157 11:23.680 --> 11:25.000 What do you want to add? 158 11:26.050 --> 11:30.550 Now this is our second parameter. 159 11:30.550 --> 11:32.470 How many days you want to add? 160 11:35.020 --> 11:37.120 How many days you want to add? 161 11:37.330 --> 11:43.650 One day or how many days you want to subtract from the current date. 162 11:44.250 --> 11:47.790 If you want to subtract, then you have to mention minus one. 163 11:48.180 --> 11:56.070 And if you want to add one day, then you want to do plus one right? 164 11:56.940 --> 11:58.620 And this is a particular date. 165 11:58.620 --> 12:06.730 So over here I can give anything like my sales date from my table. 166 12:07.360 --> 12:11.290 Then I can give you purchase date or shipping date. 167 12:11.290 --> 12:13.330 Any date I can give over here. 168 12:13.810 --> 12:17.650 But at that time, you have to mention over here from close, right? 169 12:19.810 --> 12:21.460 But now it is not required. 170 12:21.460 --> 12:25.030 I am showing letting you know the example. 171 12:25.570 --> 12:31.890 So now if I want to find the yesterday's date and today's date. 172 12:34.350 --> 12:39.510 So from the current date, I want to subtract one day. 173 12:39.510 --> 12:40.020 Right. 174 12:40.290 --> 12:43.200 And only I will get yesterday date. 175 12:43.320 --> 12:44.790 And from the current date. 176 12:44.790 --> 12:50.340 If I want to get tomorrow or ten I want to add one day. 177 12:50.340 --> 12:50.760 Right. 178 12:50.760 --> 12:52.620 So let's see over here. 179 12:54.560 --> 12:56.420 So it is giving me. 180 12:58.700 --> 13:02.450 This one is giving me yesterday date. 181 13:02.450 --> 13:05.030 And this one below one is giving me two days date. 182 13:05.030 --> 13:05.690 Sorry. 183 13:05.690 --> 13:06.500 Tomorrow's date. 184 13:06.500 --> 13:09.290 Right eight eight and six eight. 185 13:10.220 --> 13:13.820 Now let's say if I want to go ten days back. 186 13:14.090 --> 13:21.990 If I want to go ten days back means in the past, So I have to extract minus ten, right? 187 13:21.990 --> 13:25.290 I have to extract minus ten from my current date. 188 13:25.290 --> 13:27.660 So 28 two days seven. 189 13:28.110 --> 13:31.890 So it will give me 28 seven. 190 13:35.490 --> 13:38.580 Now suppose one month back right? 191 13:38.580 --> 13:40.770 This time I want to go one month back. 192 13:40.950 --> 13:48.650 So over here I have to give parameter as one month month and this one minus one. 193 13:48.650 --> 13:53.660 So it will give me, it will give me. 194 13:58.070 --> 14:05.930 772024 right 772024. 195 14:06.080 --> 14:08.590 Now if I want to go one year back. 196 14:11.050 --> 14:18.640 Then in place of month, I have to pass over year here and minus one from the current date. 197 14:18.700 --> 14:23.230 So I am now I am able to go one year back, right? 198 14:26.590 --> 14:27.370 Okay. 199 14:27.370 --> 14:29.860 Now let's see a few more example. 200 14:29.860 --> 14:29.870 examples. 201 14:31.250 --> 14:35.450 Now then, one more function is there, which is eomonth. 202 14:36.020 --> 14:40.970 So it will it will return me the last day. 203 14:40.970 --> 14:44.870 Last day of particular month. 204 14:47.780 --> 14:50.660 Last day of particular month. 205 14:51.290 --> 14:56.980 So now suppose if I mention over here eaoh month of get date. 206 14:57.010 --> 14:57.580 So what. 207 14:57.580 --> 14:58.450 It will return me. 208 14:58.450 --> 14:59.830 It will return me. 209 15:00.640 --> 15:02.530 This month is August right. 210 15:02.590 --> 15:04.360 So what is the last month? 211 15:04.360 --> 15:07.480 Last day of August, 31st August. 212 15:07.480 --> 15:07.990 Right. 213 15:08.020 --> 15:10.990 So it will return me 31st August 2002. 214 15:10.990 --> 15:15.040 For 31st August 2002. 215 15:15.040 --> 15:17.680 For now, what I want to do. 216 15:18.480 --> 15:19.380 Previous month. 217 15:19.380 --> 15:22.410 Last day I want previous month. 218 15:22.980 --> 15:30.900 So in this particular case, this one will give me current month. 219 15:30.900 --> 15:31.350 Last day. 220 15:31.350 --> 15:31.890 Right. 221 15:32.310 --> 15:34.530 But I want previous month last day. 222 15:34.620 --> 15:36.300 So what can I do. 223 15:37.110 --> 15:43.000 I can do in this particular date I can do minus one. 224 15:46.420 --> 15:48.160 I can do minus one. 225 15:48.370 --> 15:49.630 Let's see now. 226 15:57.730 --> 15:58.690 So. 227 16:06.960 --> 16:09.390 it will what it will do. 228 16:09.390 --> 16:12.810 It will it will do. 229 16:12.810 --> 16:15.420 Uh, this function will give me 31st of. 230 16:16.110 --> 16:17.340 From here it is clear. 231 16:17.340 --> 16:19.050 Right from here it is clear. 232 16:19.560 --> 16:23.460 This function will give me 31st August. 233 16:25.770 --> 16:33.500 And now I am extracting one entire month because it is eaoh month, so it will not extract one day. 234 16:34.070 --> 16:39.440 It will extract the entire month from this particular gate date. 235 16:39.440 --> 16:40.400 And then. 236 16:43.430 --> 16:44.030 Get it. 237 16:44.030 --> 16:45.500 It will retrieve me. 238 16:45.860 --> 16:49.820 So this function will give me 31st of July. 239 16:52.220 --> 16:54.710 Now, if I want the next month. 240 16:54.720 --> 16:59.790 last date, next month, last date, then again, the same thing. 241 16:59.880 --> 17:06.660 This time I will add one extra month to my this particular date. 242 17:06.660 --> 17:11.820 This particular date I will add one month. 243 17:13.920 --> 17:16.950 So it will give me 30th September. 244 17:22.130 --> 17:24.170 It will give me 30th September. 245 17:24.170 --> 17:24.740 Right? 246 17:25.220 --> 17:26.510 Now let's see. 247 17:27.050 --> 17:28.280 Particular task. 248 17:29.870 --> 17:31.340 Display the next month. 249 17:31.340 --> 17:32.660 First date. 250 17:32.690 --> 17:34.100 Display the next month. 251 17:34.100 --> 17:34.880 First date. 252 17:36.080 --> 17:40.400 Now what I want to do I want to do next month. 253 17:40.400 --> 17:41.180 First date. 254 17:41.210 --> 17:42.320 So. 255 17:45.880 --> 17:49.390 For in that particular case what I have to do Eomonth. 256 17:54.730 --> 17:59.320 Now this function will give me what this function will give me. 257 17:59.320 --> 18:02.980 31st August 2024. 258 18:03.250 --> 18:10.250 Now what I want I want to add one more day to this particular date. 259 18:10.250 --> 18:10.910 Right. 260 18:11.180 --> 18:12.710 Then only I will get. 261 18:12.710 --> 18:13.250 What? 262 18:18.650 --> 18:21.350 Then only I will get this date, right. 263 18:21.380 --> 18:27.470 So what I have done over here, I have used the date function in the dated function. 264 18:27.620 --> 18:29.990 I want to add one day. 265 18:30.380 --> 18:36.730 One day to what I want to add one day to this particular date. 266 18:39.400 --> 18:42.670 I want to add one day to this particular date. 267 18:42.970 --> 18:47.560 So I have one over here one. 268 18:50.380 --> 18:52.270 I have mentioned over here one. 269 18:52.360 --> 18:55.060 So it will give me 1st September. 270 18:55.080 --> 18:56.910 That is my requirement, right? 271 18:58.890 --> 19:02.430 It will give me 1st September now. 272 19:12.150 --> 19:13.170 Split the current month. 273 19:13.170 --> 19:13.980 First date. 274 19:14.460 --> 19:17.160 Current month okay. 275 19:17.670 --> 19:19.170 Now our requirement is. 276 19:19.170 --> 19:19.600 What? 277 19:20.440 --> 19:23.530 Display the current month. 278 19:23.530 --> 19:24.250 First date. 279 19:27.010 --> 19:28.810 Display the current month. 280 19:29.680 --> 19:30.310 First date. 281 19:36.730 --> 19:39.070 So now how can I get. 282 19:42.280 --> 19:44.730 So what function will give me? 283 19:44.730 --> 19:46.950 This function will give me. 284 19:48.870 --> 19:51.330 This function will give me 1st of September. 285 19:51.330 --> 19:51.900 Right. 286 19:52.680 --> 19:54.450 Which we have seen over here. 287 19:54.450 --> 19:56.400 Which we have seen over here. 288 19:56.760 --> 20:03.450 Now I have to do minus one, minus one from this particular date. 289 20:04.590 --> 20:05.940 So it will do. 290 20:14.420 --> 20:15.320 You good to me? 291 20:15.800 --> 20:16.610 Current month. 292 20:18.140 --> 20:20.000 It will give me current month. 293 20:21.110 --> 20:26.240 First date in one minute, guys. 294 20:30.200 --> 20:35.580 So now this minus one again, it will, uh. 295 20:37.800 --> 20:43.920 It will subtract, subtract from this particular particular date. 296 20:45.720 --> 20:49.200 It will subtract subtract it from this particular date. 297 20:49.200 --> 20:53.070 And it will give me the current month first date. 298 20:58.100 --> 21:00.410 Right now let's see. 299 21:00.410 --> 21:01.670 Let's move ahead. 300 21:08.690 --> 21:11.840 Once the date function will complete you can ask your doubt. 301 21:11.840 --> 21:12.470 We will. 302 21:12.470 --> 21:14.420 After that, we will move forward. 303 21:16.400 --> 21:19.010 So now what I want to do this time. 304 21:25.120 --> 21:27.940 Display first date of next year. 305 21:29.530 --> 21:32.110 Display first date of next year. 306 21:34.480 --> 21:39.010 So over here what we are doing. 307 21:42.970 --> 21:46.230 You Your month of get date, which will give me. 308 21:48.390 --> 21:50.940 Which will give me the current month end date. 309 21:50.940 --> 21:51.540 Right. 310 21:51.990 --> 21:52.950 Current month. 311 21:52.950 --> 21:54.060 Last date. 312 21:59.190 --> 22:02.250 Your month of get date will give me the current month. 313 22:02.430 --> 22:03.630 Last date. 314 22:03.630 --> 22:04.620 Now. 315 22:06.900 --> 22:10.750 This month has August month. 316 22:10.750 --> 22:11.860 Write August month. 317 22:12.490 --> 22:17.980 So from 12 what we need to extract from 12. 318 22:17.980 --> 22:20.650 We need to extract four. 319 22:20.650 --> 22:21.130 Right. 320 22:23.260 --> 22:23.950 Sorry. 321 22:23.950 --> 22:24.550 From. 322 22:29.020 --> 22:30.760 Brought both of this query together. 323 22:30.760 --> 22:31.600 One minute. 324 22:39.150 --> 22:48.930 Okay, so from this, uh, 12 from this 12, we we what we need to do, we need to do month of get date. 325 22:48.930 --> 22:51.000 So what is the month of get date? 326 22:51.990 --> 22:55.290 My month of get date is four, right. 327 22:55.640 --> 22:57.650 My month of get date is for. 328 23:01.190 --> 23:05.360 So over here I have shown you over here that same thing. 329 23:11.900 --> 23:12.470 Sorry. 330 23:12.470 --> 23:14.420 Month of get date is August right? 331 23:14.420 --> 23:16.040 Month of get date is eight. 332 23:16.610 --> 23:17.480 Month of eight. 333 23:17.480 --> 23:18.440 Date is eight. 334 23:18.710 --> 23:19.400 So 12. 335 23:19.400 --> 23:26.640 I have to do 12 minus eight, 12 minus eight, which is four, which is four. 336 23:26.640 --> 23:33.450 And from this August August end we need to add four months. 337 23:33.450 --> 23:36.270 From this August end we need to add four months, right? 338 23:36.270 --> 23:43.470 September, October, November and December when we added the four month, it will give me 31st December 339 23:44.210 --> 23:50.330 When we add four months it will give me 31st December 2024. 340 23:51.110 --> 23:58.220 And what I want to do, I want to do add one more day, add one more day to from this to this particular 341 23:58.220 --> 24:04.700 date so that I have added with this this function day dated. 342 24:05.420 --> 24:06.320 How many days? 343 24:06.320 --> 24:07.040 One day. 344 24:07.130 --> 24:08.530 One day to what? 345 24:09.100 --> 24:12.340 One day to this entire equation. 346 24:13.570 --> 24:16.060 One day to this entire equation. 347 24:17.140 --> 24:21.310 So for understanding purpose, I have taken this month. 348 24:21.310 --> 24:22.930 Month from date. 349 24:22.930 --> 24:23.140 Date. 350 24:23.140 --> 24:24.370 So what is the month? 351 24:24.370 --> 24:25.720 This is August month. 352 24:25.840 --> 24:28.330 So we have to do 12 minus August. 353 24:28.720 --> 24:30.280 Sorry 12 minus eight. 354 24:31.400 --> 24:37.940 So I have written four separately over here to explain you separately for now. 355 24:37.940 --> 24:43.730 This, this thing and this thing, both of this query will be the same. 356 24:45.230 --> 24:47.990 Now both of this query will be the same. 357 24:47.990 --> 24:50.090 So now what it will do. 358 24:52.670 --> 24:56.020 1st January 2025. 359 25:00.310 --> 25:04.420 Right now it will give me 1st January 2025. 360 25:06.220 --> 25:10.720 Now what I want to do display the first date of the current year. 361 25:11.530 --> 25:13.540 First date of the current year. 362 25:14.140 --> 25:21.180 So in this particular case, we just need to subtract eight. 363 25:21.660 --> 25:24.030 Subtract eight from. 364 25:27.060 --> 25:29.340 From my eomonth of get date. 365 25:29.340 --> 25:29.880 Right. 366 25:30.960 --> 25:33.090 So again it will give me. 367 25:33.360 --> 25:34.740 Let me. 368 25:37.980 --> 25:40.830 Let me try to execute this one only. 369 25:40.830 --> 25:41.940 Let me try. 370 25:45.130 --> 25:48.280 Select this particular thing. 371 25:50.410 --> 25:53.020 Now you will understand it better. 372 25:53.200 --> 25:55.030 What is it is giving me? 373 25:57.220 --> 26:03.760 It is giving me 31st December 2023. 374 26:03.760 --> 26:04.240 Right? 375 26:05.050 --> 26:06.790 Now what I want to do. 376 26:07.000 --> 26:09.660 Display the first date of the current year. 377 26:10.140 --> 26:12.420 Now my task will be very simple. 378 26:12.930 --> 26:16.320 I want to add one more day to this particular date. 379 26:18.120 --> 26:19.740 So how I have added. 380 26:22.290 --> 26:24.420 With this function dated. 381 26:29.250 --> 26:30.870 Day A comma one day. 382 26:30.870 --> 26:32.210 Comma one one to what? 383 26:32.240 --> 26:33.140 Day comma one. 384 26:33.140 --> 26:34.940 To this entire expression. 385 26:37.460 --> 26:42.170 To this entire expression I have added. 386 26:44.450 --> 26:45.380 One day. 387 26:53.150 --> 26:53.780 Right. 388 26:54.800 --> 26:57.870 So now here we are done with the date function. 389 26:57.870 --> 27:02.190 Now let me you can ask me question one minute. 390 27:02.790 --> 27:05.370 We may further see the same thing. 391 27:06.030 --> 27:09.060 Let's now move to the string functions. 392 27:10.320 --> 27:15.210 So this is the another uh function inbuilt function types. 393 27:15.960 --> 27:17.850 This time we are seeing the string function. 394 27:17.850 --> 27:25.130 So these are the mainly string functions are available in SQL or any of the relational databases. 395 27:25.790 --> 27:27.260 Upper lower length. 396 27:27.290 --> 27:28.310 Data length. 397 27:28.310 --> 27:30.980 Left right substring stuff. 398 27:30.980 --> 27:35.090 Square index replicate Ltrim Rtrim. 399 27:35.090 --> 27:35.900 Reverse. 400 27:38.960 --> 27:40.880 These are the mainly string functions. 401 27:40.880 --> 27:43.370 So let's start with the simple one upper. 402 27:43.700 --> 27:44.380 So upper. 403 27:44.380 --> 27:45.430 What will do? 404 27:45.430 --> 27:49.240 It will convert the lower case letter into the uppercase letter right. 405 27:49.690 --> 27:52.660 It will convert the lower case letter into the upper case. 406 27:53.170 --> 27:56.830 So in upper we need to give one only one parameter. 407 27:58.360 --> 27:59.950 Now what will lower do. 408 27:59.980 --> 28:05.380 So lower will convert the letter to lower case. 409 28:05.380 --> 28:11.990 So suppose I have this one And I have mixed letter like, uh, l. 410 28:16.460 --> 28:20.000 L e like this. 411 28:20.000 --> 28:25.160 So what it will do, it will convert entire thing into lower case. 412 28:25.160 --> 28:28.520 Only lower function will do the same. 413 28:31.310 --> 28:32.650 Now let's move ahead. 414 28:33.430 --> 28:39.850 Now display EMP number, name and salary from EMP table. 415 28:40.360 --> 28:43.540 Ensure that the name should be in lowercase. 416 28:44.230 --> 28:49.480 So what we can do we just doing lower of e name. 417 28:49.900 --> 28:50.260 Right. 418 28:50.260 --> 28:50.770 We can. 419 28:50.770 --> 28:51.970 We have passed over here. 420 28:51.970 --> 28:54.160 Column name over here. 421 28:54.160 --> 28:55.360 It is a default value. 422 28:55.360 --> 28:59.490 But we over here we have passed the column name. 423 28:59.490 --> 29:02.790 So MP number of column name. 424 29:02.790 --> 29:07.920 Then it will give me all the employees name into lower name. 425 29:09.870 --> 29:11.010 Now. 426 29:11.010 --> 29:16.680 Now suppose I want to update all the employee into lower name. 427 29:16.920 --> 29:21.400 So I can write this simple statement of a statement like. 428 29:21.430 --> 29:23.680 Update amp set. 429 29:24.400 --> 29:28.180 Set Ename equal to lower of Ename. 430 29:31.360 --> 29:37.300 So it will automatically update all the employees into lower name. 431 29:37.300 --> 29:38.290 Let me update. 432 29:39.580 --> 29:40.960 Now I have updated. 433 29:40.990 --> 29:43.900 Now if I see into this particular table. 434 29:50.430 --> 29:52.290 Then all the employees. 435 29:53.610 --> 29:54.390 Okay. 436 29:59.250 --> 29:59.760 Why? 437 29:59.760 --> 30:01.290 It is not updated? 438 30:02.880 --> 30:03.270 Okay. 439 30:03.270 --> 30:03.960 Yes, yes. 440 30:04.320 --> 30:04.740 Ename. 441 30:04.740 --> 30:10.400 Right now you can see all these employees are into lowercase. 442 30:14.960 --> 30:15.980 Now. 443 30:16.310 --> 30:19.370 Now one length function is there. 444 30:19.370 --> 30:20.390 So length function. 445 30:20.390 --> 30:28.370 It returns the length of the string or number of length of the string or number of characters in a particular 446 30:28.370 --> 30:29.060 string. 447 30:29.060 --> 30:32.180 So over here length function will give me. 448 30:32.880 --> 30:33.900 Hello, hello. 449 30:33.900 --> 30:37.530 So h e l l o which has five characters. 450 30:37.530 --> 30:38.070 Right. 451 30:38.460 --> 30:40.200 So it will give me five. 452 30:41.640 --> 30:47.130 Now let's say length of 123456789. 453 30:47.130 --> 30:49.380 So it will give me nine right. 454 30:49.500 --> 30:51.750 Because 1 to 9 is nine length. 455 30:52.320 --> 30:54.000 Now this particular date. 456 30:54.660 --> 30:58.430 So again it will give me four plus four plus two. 457 30:58.430 --> 31:01.070 So it will give me ten right. 458 31:01.070 --> 31:02.270 It will give me ten. 459 31:02.960 --> 31:06.260 Now over here it is important. 460 31:10.070 --> 31:12.350 Over here it is important one. 461 31:12.710 --> 31:14.120 Let's see this one. 462 31:16.940 --> 31:17.420 Over here. 463 31:17.420 --> 31:18.470 Some extra space. 464 31:18.470 --> 31:18.890 Are there. 465 31:18.890 --> 31:19.280 Right. 466 31:19.280 --> 31:20.000 Extra space. 467 31:20.000 --> 31:21.610 Are there at the beginning. 468 31:22.720 --> 31:24.010 So it will. 469 31:27.100 --> 31:30.670 This length function will consider leading space. 470 31:30.670 --> 31:36.520 Leading space means whatever the character is there and is there in before. 471 31:36.730 --> 31:42.880 If there is a before, some spaces are there, then it is known as a leading space. 472 31:42.880 --> 31:46.040 And if after there is space, ah, then. 473 31:46.040 --> 31:49.100 Then it is known as trailing space. 474 31:51.170 --> 31:53.780 So now it will consider space also. 475 31:53.780 --> 31:54.290 Right? 476 31:54.890 --> 31:56.780 It will consider space also. 477 31:56.780 --> 31:59.870 So it will give me eight by eight. 478 31:59.870 --> 32:02.810 Because over here there are three spaces. 479 32:02.810 --> 32:05.480 Are there one, two and three. 480 32:05.960 --> 32:06.620 Right. 481 32:08.590 --> 32:10.750 now this length function. 482 32:14.440 --> 32:17.380 Now this is again interesting thing. 483 32:17.830 --> 32:18.400 Length. 484 32:19.930 --> 32:24.970 Length function will not consider the trailing space. 485 32:25.000 --> 32:28.900 It will consider only the leading space. 486 32:34.110 --> 32:35.820 So now over here. 487 32:40.110 --> 32:46.950 Again, it will consider only eight y because it is not considering this trailing space. 488 32:50.910 --> 32:54.420 Now, if I want to consider that trailing space. 489 32:57.910 --> 32:59.260 Then what I have to do. 490 33:04.120 --> 33:07.630 Then I have to use this particular function data length. 491 33:09.370 --> 33:12.130 Then I have to use this particular function. 492 33:14.350 --> 33:15.250 Data length. 493 33:15.280 --> 33:17.590 So let me execute this query. 494 33:21.960 --> 33:25.140 This will give me five, but this will give me nine. 495 33:25.140 --> 33:25.650 Why? 496 33:28.200 --> 33:30.090 One space over here. 497 33:31.710 --> 33:32.940 One, two. 498 33:33.090 --> 33:34.710 Three space over here. 499 33:35.220 --> 33:36.570 Total four space. 500 33:39.120 --> 33:40.470 Plus this is five. 501 33:43.170 --> 33:45.830 Five plus four is equal to nine, right? 502 33:47.150 --> 33:49.820 Now what if this function will do? 503 33:50.930 --> 33:52.490 This is the same function. 504 33:53.630 --> 33:54.770 But we are. 505 33:55.850 --> 33:56.990 But we are. 506 33:59.750 --> 34:01.040 But we are. 507 34:03.320 --> 34:05.870 Doing writing over here n. 508 34:08.360 --> 34:11.580 So as the data is Unicode data is in. 509 34:11.580 --> 34:14.250 So this is known as a Unicode. 510 34:15.210 --> 34:22.230 So this one in the Unicode each of the character will take two space. 511 34:23.010 --> 34:26.220 Each of the character will take two space. 512 34:26.610 --> 34:28.710 So this will take two space. 513 34:28.860 --> 34:31.080 Then this will take two space. 514 34:32.460 --> 34:34.100 Then this will take two spaces. 515 34:34.100 --> 34:35.180 This will take two spaces. 516 34:35.180 --> 34:36.410 This will take two spaces. 517 34:36.710 --> 34:39.200 So this time it will give me ten. 518 34:40.550 --> 34:43.100 This time it will give me ten because of Unicode. 519 34:43.100 --> 34:43.640 Right. 520 34:47.060 --> 34:49.010 Now let's see further. 521 34:51.140 --> 34:54.560 Display the details of the employees who name. 522 34:55.820 --> 34:56.390 Who. 523 34:56.390 --> 34:57.550 Whose name? 524 35:00.010 --> 35:09.910 Whose name contains exactly five characters without using of length functions or data length function. 525 35:09.910 --> 35:10.750 So how? 526 35:16.870 --> 35:22.940 So I have told you previously in when we are using the. 527 35:25.070 --> 35:34.130 When we are using the like operator, this one will every underscore will take one space. 528 35:34.820 --> 35:36.710 This will take two space. 529 35:37.700 --> 35:39.470 This will take five space. 530 35:39.830 --> 35:41.540 So we can mention like this. 531 35:41.540 --> 35:42.230 Like. 532 35:44.330 --> 35:46.690 MP name like MP name. 533 35:46.690 --> 35:51.550 Like 12345. 534 35:59.140 --> 36:01.210 So let me execute this query. 535 36:01.210 --> 36:04.000 All the MP name are having five characters. 536 36:04.030 --> 36:04.990 See over here. 537 36:06.130 --> 36:06.790 Smith. 538 36:06.790 --> 36:07.360 Harry. 539 36:07.360 --> 36:08.230 James. 540 36:08.680 --> 36:09.100 Adam. 541 36:09.100 --> 36:10.380 Scott, Clark. 542 36:10.620 --> 36:15.960 All are having five characters in the same thing we can do using length function as well. 543 36:17.400 --> 36:21.510 Same thing we can do using length of empname equal to five. 544 36:24.360 --> 36:27.540 So both of these query will give me the same result. 545 36:29.970 --> 36:35.270 Both of these query will give me the same result right now. 546 36:35.660 --> 36:39.320 Ten rows over here and ten rows over here. 547 36:41.480 --> 36:43.580 Now let's see the left function. 548 36:45.710 --> 36:51.590 It returns the specified number of characters from the left side or start of the string. 549 36:52.250 --> 36:52.910 Right. 550 36:53.450 --> 36:54.440 So. 551 36:57.660 --> 37:07.500 So now what I want I want the all the employee name who is starting with J whose first character should 552 37:07.500 --> 37:07.950 be J. 553 37:09.090 --> 37:10.230 First character. 554 37:10.680 --> 37:11.760 So what can I do? 555 37:11.790 --> 37:19.320 One thing I can do is I can use the like operator like this, which we have seen already. 556 37:19.410 --> 37:26.210 And another thing we can do left of EMP name comma one equal to j. 557 37:27.260 --> 37:30.560 So both of these query will give me the same result. 558 37:34.550 --> 37:35.570 Don James Jerry. 559 37:37.070 --> 37:39.890 Both of these query give me the same result. 560 37:40.220 --> 37:41.780 Now right function. 561 37:42.230 --> 37:47.410 So display the EMP employee details whose name ends with S. 562 37:50.380 --> 37:51.460 Ends with s. 563 37:51.880 --> 37:56.530 So again we can do with the like as well as write operator right function. 564 37:57.310 --> 38:06.970 So let me show you all this name are ends with S33 rows are there right in both of the query. 565 38:09.200 --> 38:12.020 Now very interesting question. 566 38:15.290 --> 38:16.220 Display. 567 38:16.550 --> 38:26.060 Display the details of the employees whose name starts and ends with the same character, whose name 568 38:26.060 --> 38:30.290 starts and ends with the same character. 569 38:30.440 --> 38:31.730 Like suppose if. 570 38:31.730 --> 38:32.540 Harshitha. 571 38:35.200 --> 38:39.550 Then Harshitha will start with H and it will end with H as well. 572 38:40.180 --> 38:41.350 So what can I do? 573 38:41.350 --> 38:43.360 I can simply write like this. 574 38:44.620 --> 38:46.930 Select star from amp. 575 38:46.930 --> 38:47.800 Where? 576 38:48.580 --> 38:49.270 Where? 577 38:49.300 --> 38:50.470 Left of amp. 578 38:50.470 --> 38:55.660 Name comma one equal to right of name comma one. 579 38:56.020 --> 39:01.020 So over here starting first character and ending first character should be same. 580 39:01.920 --> 39:04.050 So let me show you the result. 581 39:05.220 --> 39:10.200 It will give me harshitha right over here. 582 39:10.200 --> 39:11.490 H is also there. 583 39:11.820 --> 39:13.980 And over here H is also there. 584 39:17.700 --> 39:19.890 So this is my query. 585 39:26.950 --> 39:31.780 Now suppose let me show you one more thing. 586 39:35.590 --> 39:37.960 Select star from AMP. 587 39:39.910 --> 39:42.940 Now I have this particular table are there right. 588 39:43.420 --> 39:51.420 Now I want to generate the employee email address from this particular table, but I want three characters 589 39:51.420 --> 39:52.740 from this one. 590 39:55.170 --> 40:02.970 I want three character top means left, three character from this one, and left three characters from 591 40:02.970 --> 40:04.200 this one as well. 592 40:05.400 --> 40:09.510 It means seven, three, six. 593 40:09.950 --> 40:12.800 Then s m I. 594 40:15.440 --> 40:18.920 And then I can do any of the email address like, uh. 595 40:21.200 --> 40:22.910 Microsoft.com. 596 40:24.350 --> 40:27.530 So I want to generate that kind of email address. 597 40:28.010 --> 40:34.770 Then what I have to do left of EMP name comma three let left of MP name comma three. 598 40:34.770 --> 40:38.610 Plus what I have to do, I have to do concat, right? 599 40:38.610 --> 40:48.420 I have to do concat of all these three, concat of this one, then concat of this one, and concat of 600 40:49.410 --> 40:54.480 this one means I want to do concat of this one and concat of this one. 601 40:54.480 --> 40:55.050 Right? 602 40:57.980 --> 41:02.930 So now I can do that with the plus operator as well. 603 41:03.380 --> 41:06.770 So now let me try to execute this particular query. 604 41:07.580 --> 41:11.990 What it will give me, it will give me like this. 605 41:11.990 --> 41:12.590 Right. 606 41:17.180 --> 41:19.130 Which I have mentioned over here. 607 41:21.550 --> 41:22.870 John 756. 608 41:22.870 --> 41:26.260 If you see one more so it will again give me. 609 41:27.520 --> 41:33.220 This time we we have this, this column is fixed, so that is fine. 610 41:35.410 --> 41:40.540 When we have a dynamic dynamic we will see in at that time. 611 41:40.540 --> 41:45.490 So now what we have to do I have to alter this particular table to add email column. 612 41:45.490 --> 41:46.010 Right. 613 41:46.910 --> 41:54.200 I have to do email, email ID okay I have already email ID is there, but suppose email ID is not there. 614 41:54.200 --> 42:01.010 Then I have to add this email ID column and then simply I have to update this right. 615 42:01.040 --> 42:05.330 Update emp set email id email id equal to what? 616 42:06.050 --> 42:08.960 Email id equal to all this entire thing. 617 42:10.780 --> 42:11.650 Entire thing? 618 42:11.650 --> 42:12.130 I have to. 619 42:12.160 --> 42:13.360 It is already there. 620 42:13.900 --> 42:16.570 Let me show you the entire thing like this. 621 42:18.550 --> 42:20.050 And I can mention comma. 622 42:20.050 --> 42:22.090 So it will update everything. 623 42:22.090 --> 42:24.880 Every email ID with left of first name. 624 42:25.000 --> 42:25.510 Sorry. 625 42:25.510 --> 42:30.550 Left of three characters from the name and three characters from the EMP number. 626 42:32.980 --> 42:35.400 Why it is giving error I think. 627 42:41.100 --> 42:43.320 Okay I think email ID column is not there. 628 42:43.320 --> 42:45.060 Let me add it over here. 629 42:49.380 --> 42:53.880 Okay I have added the column and now I can mention this column over here. 630 42:57.120 --> 42:58.720 Now I can update. 631 42:59.050 --> 43:03.700 17 records are updated and now it should be there. 632 43:03.700 --> 43:06.340 Select star from. 633 43:12.430 --> 43:13.030 IMDb. 634 43:14.590 --> 43:16.180 So it is available right? 635 43:18.910 --> 43:20.800 Okay so we will keep up to here. 636 43:20.800 --> 43:22.930 Now let me stop the recording.