1 00:01.670 --> 00:07.820 So today we will see the built in, uh, built in functions in SQL server. 2 00:08.210 --> 00:12.080 So basically what are the functions. 3 00:12.080 --> 00:21.440 Let's see that first functions are designed to perform some specific task in some functions takes argument 4 00:21.440 --> 00:29.080 like if you use this uh, some functions means some of the functions like some functions. 5 00:29.080 --> 00:32.650 When you use some function, then you have to give arguments. 6 00:32.650 --> 00:37.840 So whatever you written over here that is known as argument right. 7 00:38.980 --> 00:41.950 Suppose if you use the average as well. 8 00:41.950 --> 00:50.680 So again it will take the argument for which which value you want to do the average. 9 00:50.680 --> 00:57.620 So for that function it will be used in some of the functions will not take argument. 10 00:57.620 --> 01:02.180 Suppose if I use like select get date. 11 01:02.180 --> 01:04.520 So it will give me today's date. 12 01:04.520 --> 01:10.130 So in the bracket we are not taking a we are not giving anything right. 13 01:10.130 --> 01:13.460 So it is a argument free right. 14 01:13.460 --> 01:16.970 So it will give me today's date. 15 01:18.490 --> 01:20.740 Today's date with timestamp. 16 01:22.150 --> 01:26.680 So what are the different SQL functions available in. 17 01:28.090 --> 01:31.420 What are the different functions available in SQL server. 18 01:31.660 --> 01:34.300 So let's see that one. 19 01:34.420 --> 01:37.150 So mainly it is date and date time functions. 20 01:37.330 --> 01:44.760 You can told then string functions then mathematical functions, then conversion functions, then special 21 01:44.760 --> 01:49.170 functions, then analytical functions and aggregate functions. 22 01:49.170 --> 01:52.710 So mostly these are the seven types of functions are there. 23 01:52.830 --> 01:54.870 And whatever is written over here. 24 01:54.870 --> 02:00.930 So these are the functions are part of the date time functions like date part date name. 25 02:01.080 --> 02:06.880 Then this will be the part of the conversion functions like cash, convert and pass. 26 02:09.550 --> 02:10.630 Then these are. 27 02:10.630 --> 02:14.080 The functions are the part of the special functions. 28 02:14.110 --> 02:17.170 So let's start with the date functions first. 29 02:17.800 --> 02:22.870 So these are the date functions are available right. 30 02:22.870 --> 02:28.450 Date part date name date diff dated year month year month and day. 31 02:29.040 --> 02:34.230 So let's start with the date and date time function. 32 02:34.230 --> 02:39.270 So what is the first function we are starting with which is known as a year? 33 02:39.810 --> 02:46.740 So suppose suppose I have some particular date available with me right. 34 02:46.800 --> 02:49.470 Suppose today's date 2024. 35 02:50.220 --> 02:53.450 This zero eight date zero 02408806. 36 02:53.900 --> 03:03.050 So from this particular value I want to extract this year only this year only. 37 03:03.050 --> 03:05.270 So at that time what will we do. 38 03:05.300 --> 03:06.590 Select year. 39 03:07.100 --> 03:08.330 Select year. 40 03:08.810 --> 03:16.160 Then this specific date will be given in the single quote parameter. 41 03:16.160 --> 03:20.100 And then as we have given alias as here. 42 03:20.640 --> 03:26.310 So here we are not using any table right here we are not using any table. 43 03:26.310 --> 03:28.320 So from clause is not required. 44 03:37.200 --> 03:44.480 Because we are not using any table so directly, we can write this function. 45 03:44.840 --> 03:46.610 Select year of two zero. 46 03:46.610 --> 03:48.500 So what will be the answer of this one? 47 03:48.500 --> 03:48.950 It will be. 48 03:48.950 --> 03:51.650 The answer is 2021. 49 03:52.940 --> 03:56.120 So it will give me the year of this particular. 50 03:58.700 --> 03:59.720 Parameter. 51 04:01.550 --> 04:03.710 Now for same for the month. 52 04:03.710 --> 04:09.880 So if I want to retrieve from this particular date, I want to retrieve month. 53 04:10.300 --> 04:15.010 So then in place of year we you need to pass over here month. 54 04:15.400 --> 04:19.870 So let me write it. 55 04:19.870 --> 04:23.260 So you have to remember one thing. 56 04:27.520 --> 04:32.540 This this function will return the integer value right. 57 04:32.540 --> 04:35.780 Returns month part of date as integer. 58 04:36.350 --> 04:42.080 So it will not give me the name of this particular month. 59 04:42.080 --> 04:43.160 It will give me the year. 60 04:43.160 --> 04:50.630 So whatever there is in the middle, it is a month and this is a date and this is a year. 61 04:51.200 --> 04:53.740 So it will give me forth as a month. 62 04:56.470 --> 04:58.810 Now same for the day. 63 04:58.810 --> 05:05.440 So over here, what it will return me, it will return me integer value and it will return me zero, 64 05:05.440 --> 05:06.430 5 or 5. 65 05:06.460 --> 05:07.240 Right. 66 05:07.240 --> 05:08.770 Fifth is a day. 67 05:11.740 --> 05:15.790 So these are the year, month and date functions. 68 05:15.820 --> 05:17.590 Now let's move ahead. 69 05:18.750 --> 05:20.400 Now let's move ahead. 70 05:21.300 --> 05:23.940 List the with the query. 71 05:23.940 --> 05:27.960 Now we are using the same function with the query. 72 05:28.260 --> 05:32.010 So now this is our question. 73 05:32.010 --> 05:36.660 Display the list of the employees joined in the year 2021. 74 05:37.500 --> 05:41.340 So we need to display all the employee details. 75 05:41.350 --> 05:44.170 So I have write select star from EMP where. 76 05:44.800 --> 05:51.910 Now what we have to mention, we have to mention here should be 2021. 77 05:52.240 --> 05:59.590 But for that year column how we can identify from which column we need to extract the error. 78 05:59.860 --> 06:02.500 So we have hired it is there right. 79 06:02.890 --> 06:06.480 We have only one date column is there and that is higher date. 80 06:06.930 --> 06:10.050 So from this higher date we need to extract here. 81 06:10.080 --> 06:11.580 How can we extract? 82 06:11.580 --> 06:15.660 Year of higher date is equal to 2021. 83 06:18.600 --> 06:28.020 So only one employee is there from this entire table which was joined in 2021. 84 06:28.020 --> 06:31.150 Suppose I want to take 1981. 85 06:31.150 --> 06:32.830 So let me try. 86 06:37.780 --> 06:40.450 So now this time employee will be more right. 87 06:40.450 --> 06:45.520 All this employee if you see over here year is 1981. 88 06:45.520 --> 06:50.740 In the higher date column right now let's move ahead. 89 06:52.180 --> 06:57.540 So now this same thing we can previously seen with this like operator. 90 06:57.540 --> 06:58.320 Right. 91 06:58.710 --> 07:02.730 Suppose I mention over here 1981. 92 07:02.730 --> 07:11.340 So both of these query higher date like 1981 and year of higher date is equal to 1981 will give me the 93 07:11.340 --> 07:15.960 same number of rows here also ten rows and here also ten rows. 94 07:16.140 --> 07:19.880 So both will be giving me the same number of rows right. 95 07:22.040 --> 07:23.780 Now let's move ahead. 96 07:25.010 --> 07:30.890 Display the list of the employees joined in January this April or December. 97 07:32.690 --> 07:35.090 So here will be anything. 98 07:35.090 --> 07:38.510 Here will be anything but here higher date. 99 07:38.510 --> 07:42.330 So again we have seen this month function. 100 07:42.330 --> 07:43.830 So month of higher date. 101 07:43.830 --> 07:44.640 So what. 102 07:44.640 --> 07:47.130 This month of this will retrieve. 103 07:47.130 --> 07:48.480 It will retrieve for. 104 07:48.480 --> 07:53.430 So over here we need to identify either April means zero one. 105 07:54.720 --> 07:56.760 If you mention one then also fine. 106 07:56.760 --> 08:02.910 If you mention zero one then also fine then April zero four and then December 12th. 107 08:02.910 --> 08:10.010 So all this employee, if you see 12 is a month, zero four again is a month, zero one is a month, 108 08:11.180 --> 08:13.820 12 is again a month over here two times. 109 08:13.820 --> 08:17.750 So all this employee here will be anything 1980. 110 08:17.780 --> 08:19.010 That will be fine. 111 08:19.010 --> 08:20.330 1982. 112 08:20.330 --> 08:25.730 That will be fine 1981 202 for here is not here a question here. 113 08:25.730 --> 08:30.850 Question is month should be either zero one, zero 4 or 12. 114 08:33.340 --> 08:36.010 So let's see another query. 115 08:36.670 --> 08:43.150 Display the list of employees who join in 2021 and January month. 116 08:43.270 --> 08:50.170 So here what we need to do here we need to write end condition here should be 2021. 117 08:50.680 --> 09:00.020 So let me execute up to here only here should be 2021 and month should be January. 118 09:00.020 --> 09:04.460 So this employee has been joining January as well right. 119 09:04.790 --> 09:07.820 So it will give me same result. 120 09:08.120 --> 09:11.180 Now let's see for another one. 121 09:14.510 --> 09:15.080 Here. 122 09:15.080 --> 09:17.750 Let's see 1981 and February. 123 09:17.770 --> 09:22.360 So what I changed 1981 and here zero two. 124 09:22.930 --> 09:30.280 So let me so this two employees are joining 1981 and February month. 125 09:30.280 --> 09:30.670 Right. 126 09:30.670 --> 09:32.920 Because zero two is mentioned in the middle. 127 09:39.040 --> 09:44.070 Now let's see the further further queries. 128 09:44.310 --> 09:51.660 So this date time this is one more function since date time returns the current date and time of the 129 09:51.660 --> 09:52.530 system. 130 09:52.560 --> 09:54.540 So let me see. 131 09:56.550 --> 10:00.510 It will return me the current date and time of the system. 132 10:00.510 --> 10:02.400 So what is the today's date? 133 10:02.730 --> 10:07.420 Six of August 10th, 2054. 134 10:07.420 --> 10:10.300 And this will be, you can say is a higher precision. 135 10:11.080 --> 10:14.440 Or you can say what is this? 136 10:24.760 --> 10:28.660 High y high precision. 137 10:28.660 --> 10:38.010 Remember one thing a this query is giving the volt after decimal the volt result. 138 10:38.010 --> 10:40.710 It will not cut the result like uh. 139 10:42.780 --> 10:47.280 Like it will not cutting the result like this or it is not giving the result like this. 140 10:47.280 --> 10:52.320 It is giving me the entire result, whichever should be there. 141 10:52.320 --> 10:57.110 So that is why it is known as a higher precision or high precision. 142 10:57.920 --> 11:00.050 Now let's see another example. 143 11:02.600 --> 11:03.380 Getdate. 144 11:03.380 --> 11:05.360 So we have already seen this one. 145 11:05.360 --> 11:07.130 So let's see this one. 146 11:08.750 --> 11:16.550 Now carefully observe over here it is ignoring this four digit right into this particular getdate. 147 11:16.670 --> 11:20.490 So you can consider get date as a low precision. 148 11:23.310 --> 11:26.850 You can consider this get date as a low precision. 149 11:27.630 --> 11:28.830 Now. 150 11:34.950 --> 11:36.360 Now let's see. 151 11:38.400 --> 11:39.360 Further. 152 11:39.390 --> 11:41.970 So what is the current timestamp? 153 11:42.200 --> 11:47.510 Returns the current date and time of the system and it is a low low precision. 154 11:47.510 --> 11:50.720 So let me okay. 155 11:50.720 --> 11:54.110 Now let me execute both of these query together. 156 11:54.680 --> 11:57.260 Either it is giving same result or not. 157 11:58.070 --> 12:00.560 Yeah it is giving me the same result. 158 12:02.690 --> 12:04.400 It is giving me the same result. 159 12:04.400 --> 12:12.880 So you can use either get date or you can use current timestamp in both are of low precision. 160 12:16.210 --> 12:19.060 Both are of low precision. 161 12:20.830 --> 12:28.120 Right now let's say UTC UTC date time. 162 12:28.120 --> 12:34.790 So what is UTC Coordinated Universal Time based on Earth rotation? 163 12:35.990 --> 12:41.900 So you can see this UTC time many a times when you are, uh, setting the time zone. 164 12:41.900 --> 12:42.500 Right. 165 12:42.500 --> 12:48.560 So that is that UTC and it has some difference in IST time zone. 166 12:48.560 --> 12:51.410 Our India is a ist Indian standard time zone. 167 12:51.410 --> 12:51.950 Right. 168 12:52.250 --> 12:55.510 So this this will be the time will be different over here. 169 12:55.540 --> 12:57.040 So let's see. 170 12:58.840 --> 13:02.200 So it is showing 455. 171 13:03.400 --> 13:05.470 So few hours. 172 13:05.470 --> 13:08.170 We are ahead from this UTC time right. 173 13:08.710 --> 13:12.280 We are ahead of few hours from this UTC time. 174 13:12.400 --> 13:14.380 And again it is a higher precision. 175 13:14.380 --> 13:14.920 Why? 176 13:15.430 --> 13:18.990 Because it is giving me this entire result. 177 13:19.440 --> 13:22.440 Now, uh, let's run the below query. 178 13:22.890 --> 13:24.840 Get UTC date. 179 13:25.080 --> 13:34.170 So whenever you are including time, it will be higher precision and when you are may not include time, 180 13:34.170 --> 13:36.810 it may be low precision. 181 13:36.810 --> 13:38.250 So let's see over here. 182 13:38.820 --> 13:40.590 So over here 163. 183 13:40.590 --> 13:42.060 Let me run it again. 184 13:43.480 --> 13:44.170 Okay. 185 13:44.170 --> 13:48.850 So over here it is 488. 186 13:50.800 --> 13:55.030 After four, eight, eight this four digit is not showing over here. 187 13:55.030 --> 13:55.270 Right. 188 13:55.270 --> 13:58.450 It is showing over here some random 487. 189 13:58.450 --> 14:03.310 But last four digit is not showing over here in this below one. 190 14:03.490 --> 14:05.230 So this is high precision. 191 14:05.230 --> 14:13.020 And this is known as low precision because it is more perfect more perfect result. 192 14:13.260 --> 14:17.010 That is why it is known as high precision. 193 14:17.160 --> 14:18.930 Now let's move ahead. 194 14:21.990 --> 14:27.180 Now see let's see some of the date functions we are. 195 14:30.000 --> 14:30.680 We are. 196 14:30.680 --> 14:32.960 Uh oftenly. 197 14:32.960 --> 14:36.020 Oftenly we are using in our day to day life. 198 14:36.020 --> 14:38.120 Most probably so. 199 14:38.120 --> 14:40.610 One of them is date part function. 200 14:41.240 --> 14:50.660 So now used to extract the part of the date or date time used to extract the part of a date or date 201 14:50.690 --> 14:51.410 time. 202 14:51.770 --> 14:53.600 It returns integer. 203 14:54.600 --> 14:56.010 It returns integer. 204 14:56.040 --> 14:56.850 Now syntax. 205 14:56.850 --> 14:58.080 What is the syntax? 206 14:58.260 --> 14:59.460 Date part. 207 15:01.200 --> 15:06.690 Then some particular date part from given date. 208 15:06.720 --> 15:10.290 What date part you want to return from. 209 15:10.290 --> 15:11.040 Given date. 210 15:11.070 --> 15:13.170 What date part you want to return. 211 15:13.170 --> 15:15.510 So that will be comes over here. 212 15:16.170 --> 15:17.760 And this will be my. 213 15:19.790 --> 15:20.840 Actual value. 214 15:20.840 --> 15:22.580 Like, uh, suppose. 215 15:24.650 --> 15:26.630 Let me copy this one. 216 15:30.080 --> 15:37.100 Now let me copy this 12021-01 12. 217 15:37.100 --> 15:42.020 Now suppose again I want to return here from this particular date. 218 15:42.350 --> 15:50.410 Then I have to mention here, over here and remember one thing in this particular case. 219 15:53.710 --> 15:56.080 This both will return the same thing. 220 15:58.990 --> 16:01.150 This both will return the same thing. 221 16:01.150 --> 16:01.870 Let me. 222 16:10.580 --> 16:12.110 Let me try. 223 16:13.100 --> 16:18.080 See, in this particular scenario, both will be written the same thing. 224 16:18.080 --> 16:23.630 So you should be aware what are the different ways to extract different things. 225 16:23.750 --> 16:28.580 But this date part part will provide more facilities as well obviously. 226 16:28.580 --> 16:30.620 So that we will see again. 227 16:30.670 --> 16:34.960 So in the date part it will take two arguments. 228 16:36.880 --> 16:39.400 Most probably it will take two arguments. 229 16:39.400 --> 16:44.800 So these are the different arguments I have written your quarter. 230 16:44.800 --> 16:51.280 So if you want to retrieve quarter from this particular date right then you have to mention over here 231 16:51.280 --> 16:51.910 quarter. 232 16:54.400 --> 16:58.200 But this facility is not provided over here directly, right? 233 17:00.030 --> 17:00.330 Can you. 234 17:00.330 --> 17:02.430 Can we retrieve Qatar from this one? 235 17:02.430 --> 17:04.650 No, we cannot retrieve. 236 17:04.650 --> 17:09.210 But from this one, yes, we can retrieve the Qatar. 237 17:09.240 --> 17:12.480 Qatar means January to March. 238 17:12.480 --> 17:14.820 Quarter one, April to June. 239 17:15.270 --> 17:16.110 Quarter two. 240 17:16.140 --> 17:17.100 July to September. 241 17:17.100 --> 17:17.790 Quarter three. 242 17:17.790 --> 17:18.840 October to December. 243 17:18.850 --> 17:19.630 Quarter four. 244 17:21.400 --> 17:22.810 Now what will be the week? 245 17:22.810 --> 17:24.250 Day so so week. 246 17:24.250 --> 17:30.160 If you want to retrieve the week day, then week day will be you have to mention in argument. 247 17:30.160 --> 17:31.480 You have to mention weekday. 248 17:31.600 --> 17:34.930 Then Sunday will be S01 Monday. 249 17:34.930 --> 17:37.030 Sunday will start with Sunday. 250 17:37.030 --> 17:38.260 So Sunday one. 251 17:38.260 --> 17:42.820 Monday two, Tuesday three, Wednesday four, Saturday seven. 252 17:42.840 --> 17:43.560 Like that. 253 17:43.560 --> 17:47.010 It will give me ten from particular date time. 254 17:47.010 --> 17:51.810 If you want to retime a hour then you have to mention like this hour as well. 255 17:51.810 --> 17:53.250 So let's see. 256 17:53.760 --> 18:00.600 Suppose over here a date part is used right? 257 18:00.630 --> 18:02.760 And over here month is used. 258 18:02.760 --> 18:06.210 So now let's see one example. 259 18:08.300 --> 18:11.570 Over here in the date part I have mentioned. 260 18:14.330 --> 18:17.510 Get date get date as second argument. 261 18:17.510 --> 18:21.890 So from which date I want to retrieve. 262 18:21.890 --> 18:23.060 What thing. 263 18:23.240 --> 18:25.070 So that which date. 264 18:25.070 --> 18:27.800 That should be mentioned in the second argument. 265 18:27.800 --> 18:29.840 And what I want to retrieve. 266 18:29.900 --> 18:32.820 There should be mentioned in the first argument. 267 18:33.690 --> 18:40.320 Now, in this particular case, all three things, all three things will return me the same thing. 268 18:40.320 --> 18:50.130 Let's say you can mention either month, or you can mention either M or you can mention either m in 269 18:51.240 --> 18:57.350 in terms of month requirement in the date part function. 270 18:57.350 --> 19:07.070 So it will return me eighth month right August month because date part will always return in integer 271 19:07.640 --> 19:08.540 value, right? 272 19:08.570 --> 19:11.240 Always some function will return something. 273 19:11.420 --> 19:14.840 So date part will retrieve me integer value. 274 19:14.840 --> 19:21.000 And in this particular case all these three things will be written me the same thing. 275 19:21.990 --> 19:24.660 Same for this scenario as well. 276 19:24.990 --> 19:27.870 Let's say date of the year. 277 19:27.900 --> 19:29.490 Write date of the year. 278 19:30.180 --> 19:33.480 So date of the year D or year. 279 19:34.260 --> 19:37.920 So I have mentioned over here date of the year D or year. 280 19:38.490 --> 19:40.380 So it will given me. 281 19:46.760 --> 19:47.450 Sorry. 282 19:47.450 --> 19:50.150 It is a day of the year, not day of the year. 283 19:50.150 --> 19:51.500 Sorry date of the year. 284 19:51.500 --> 19:56.810 So day of the year means today is 6th of August. 285 19:56.990 --> 20:00.110 So in a year we have 365 days, right? 286 20:00.200 --> 20:04.220 So today's date is two one ninth day of the year. 287 20:05.240 --> 20:07.900 Two one ninth day of the year. 288 20:07.990 --> 20:08.860 Right. 289 20:09.160 --> 20:11.770 In a year, we have total 365 days. 290 20:12.730 --> 20:15.310 So this is known as day of the year. 291 20:15.520 --> 20:18.730 Now again, let's see a week. 292 20:18.760 --> 20:24.100 So today is sum from entire year. 293 20:24.190 --> 20:28.060 We have I think 52 weeks are there right 52 weeks. 294 20:28.090 --> 20:32.340 So today's particular date which week is there? 295 20:34.140 --> 20:35.970 So let's see. 296 20:36.780 --> 20:39.120 It is a 32nd week. 297 20:39.870 --> 20:41.790 Today is 32nd week. 298 20:41.790 --> 20:52.320 So how you can write either week or w k or w w how you can find you can find it over here. 299 20:53.130 --> 20:56.500 You can find it over here Week of the year. 300 20:56.500 --> 21:00.970 Either you can write week or w k or w w. 301 21:03.730 --> 21:04.150 Okay. 302 21:04.150 --> 21:06.850 Now let's see in more detail. 303 21:08.470 --> 21:17.050 So I want to find the year year from the particular date then year of particular parameter. 304 21:17.050 --> 21:21.360 Apart from that function we have one more function that is datepart. 305 21:21.600 --> 21:30.180 Now what I want, I want here from this particular given date so I can write date part of year comma 306 21:30.180 --> 21:31.170 get date. 307 21:31.380 --> 21:37.260 So it will give me 2024 right now now for month. 308 21:37.290 --> 21:45.760 So again you have to mention date part month of get date then get sorry month comma get date. 309 21:45.760 --> 21:47.170 So it will give me eight. 310 21:48.100 --> 21:49.540 Now what is the quarter? 311 21:49.570 --> 21:53.950 Today's quarter you can say January to March. 312 21:53.950 --> 21:56.980 Quarter 1st April to June quarter two to. 313 21:57.130 --> 22:00.070 So today's August will be quarter three. 314 22:00.160 --> 22:02.920 So it will give me quarter three right. 315 22:04.930 --> 22:09.690 You have to mention quarter as first argument and get date as second argument. 316 22:09.900 --> 22:13.140 Now what is the day of the year today? 317 22:13.770 --> 22:15.630 It is 219. 318 22:22.320 --> 22:25.140 Now what is the day? 319 22:25.170 --> 22:31.410 So this both of the thing is different day of the year and day right? 320 22:32.990 --> 22:36.650 So day means from a current week. 321 22:38.600 --> 22:41.150 Day of the month six. 322 22:45.800 --> 22:49.820 So today is it is giving me today's date to today's. 323 22:49.820 --> 22:53.090 From today's date it is giving me a day. 324 22:53.090 --> 22:55.430 So today is 6th of August right. 325 22:55.860 --> 23:01.260 Today is 6th of August so it will giving me six. 326 23:02.310 --> 23:06.270 Now let's see further week of the year. 327 23:06.270 --> 23:13.740 So week today is I think 32nd week right that we have seen already. 328 23:13.740 --> 23:20.390 So it will give me 32nd now today's Monday. 329 23:21.350 --> 23:22.040 Sorry. 330 23:22.040 --> 23:25.880 Today we can say today is Tuesday. 331 23:25.910 --> 23:27.680 Now what it will give me. 332 23:27.890 --> 23:30.380 So Sunday is a week day. 333 23:30.380 --> 23:32.960 Weekday means starting from the week. 334 23:32.960 --> 23:34.190 Sunday week. 335 23:34.190 --> 23:36.110 Day one Monday week. 336 23:36.110 --> 23:36.770 Day two. 337 23:37.490 --> 23:38.510 Today's Tuesday. 338 23:38.510 --> 23:40.160 So weekday should be three. 339 23:40.460 --> 23:43.130 Day of the week three. 340 23:45.520 --> 23:49.000 Now let's see further over at today. 341 23:49.000 --> 23:51.880 Right now it is ten right. 342 23:51.880 --> 23:54.610 So it will give me ten. 343 23:57.850 --> 24:01.150 Ten minute 2627. 344 24:01.510 --> 24:01.900 Okay. 345 24:01.900 --> 24:02.800 26. 346 24:02.800 --> 24:07.420 Now if I run now then it will give me 27. 347 24:08.180 --> 24:12.710 It will take my system, my system from my system. 348 24:12.710 --> 24:14.270 It is taking everything. 349 24:16.580 --> 24:19.280 Second, so it will keep changing, right? 350 24:19.730 --> 24:29.450 Whenever I execute 18, 20, 21, 23, 23, 24, it will changing each of the second. 351 24:32.740 --> 24:36.610 Now let's see a few of the more examples of date part. 352 24:37.630 --> 24:42.670 So display the list of employees joined in year 2021. 353 24:44.110 --> 24:46.480 So one thing. 354 24:46.480 --> 24:48.430 We have already seen this one, right? 355 24:48.430 --> 24:51.370 Zero fire date equal to 2021. 356 24:51.370 --> 24:54.760 But one more way is date part. 357 24:55.770 --> 25:01.200 Either you can write y y, so it will give me one employee. 358 25:01.230 --> 25:03.150 Either you can write. 359 25:06.420 --> 25:08.580 Here it will be the same. 360 25:08.580 --> 25:12.600 Both of these query will give me the same result, right? 361 25:13.290 --> 25:16.440 This one employee is joined in 2021. 362 25:16.560 --> 25:24.640 So whichever you think you have done in the year previously, year of given date, the same thing you 363 25:24.640 --> 25:26.710 have done with the date part function. 364 25:26.950 --> 25:34.630 So this is a very good this functions will be used at the time of doing data analysis. 365 25:36.370 --> 25:39.580 Right now let's see one more example. 366 25:39.610 --> 25:45.540 Display the list of the employees joined in January, April or December month. 367 25:45.660 --> 25:48.450 So what you have to do. 368 25:48.480 --> 25:50.160 Select star from employee. 369 25:50.190 --> 25:50.460 Where. 370 25:50.460 --> 25:51.330 Date part. 371 25:51.690 --> 25:52.470 Date part. 372 25:52.500 --> 25:55.320 Then we have to consider month of higher date. 373 25:55.320 --> 25:55.890 Right. 374 25:56.730 --> 25:57.360 Month. 375 25:57.360 --> 25:57.690 Comma. 376 25:57.690 --> 26:02.130 Higher date January, April or December. 377 26:02.130 --> 26:08.150 So all these employee are either joined in April, December or January. 378 26:08.150 --> 26:08.690 Right. 379 26:10.220 --> 26:17.810 Now again, which example we have seen above with the year end month function, that same example over 380 26:17.810 --> 26:20.870 here we are saying with date part function. 381 26:21.380 --> 26:27.110 So display the list of employees who join in either two, zero, two, one and January. 382 26:27.110 --> 26:31.790 Sorry, not either who join in 202 1st January month. 383 26:31.830 --> 26:42.960 So you have to mention like date part year of higher date equal to 2021 and date part of month. 384 26:46.410 --> 26:48.690 Of higher date equal to January month. 385 26:48.690 --> 26:53.910 So this one employee is joined in January 2nd 021. 386 26:55.430 --> 26:58.040 So we have seen the date part function. 387 26:58.040 --> 27:00.320 Now let's see the date name function. 388 27:01.550 --> 27:09.350 So there will be only one difference in date part and date name date part will return the values in 389 27:09.350 --> 27:18.830 the integer format, whereas date name function will return the values in the vector format where. 390 27:18.830 --> 27:19.370 Care. 391 27:20.890 --> 27:24.280 format date name returns end where care or where care. 392 27:24.850 --> 27:26.350 So now let's see. 393 27:31.600 --> 27:34.840 Now let's see this particular query. 394 27:34.840 --> 27:37.750 Write a query to display the day of the week. 395 27:38.980 --> 27:44.390 Day of the week on which particular day India got independence. 396 27:45.860 --> 27:51.980 Day of the week should be like Sunday, Monday, Tuesday, Wednesday like that. 397 27:53.630 --> 27:58.760 So we got independence on 15th August 1947. 398 27:58.790 --> 28:07.130 So on that particular date, whether it was Sunday, Monday, Friday, what? 399 28:07.430 --> 28:18.400 So for that particular thing, we need to write date, name, weekday in second date from which date 400 28:18.400 --> 28:21.940 we want to extract particular information. 401 28:21.940 --> 28:24.280 So let me execute this query. 402 28:25.000 --> 28:26.500 It will be a Friday. 403 28:26.500 --> 28:31.660 We got independence on Friday right. 404 28:33.130 --> 28:35.530 now let's see a few more examples. 405 28:39.670 --> 28:46.240 Display the list of employees joined in 2021 using date name function. 406 28:46.720 --> 28:53.650 So we have seen the same thing in 2021 using date part. 407 28:53.650 --> 28:56.500 But now we want to see as a date name. 408 28:56.680 --> 28:58.000 So same query. 409 28:58.000 --> 29:03.820 We are just changing the function name from date part to date name. 410 29:03.940 --> 29:10.030 And here I'd ideally this parameter should be worker. 411 29:10.630 --> 29:16.600 But over here we are not mentioning this into worker. 412 29:16.720 --> 29:21.840 And still it is integer and still it is giving us the correct result. 413 29:21.840 --> 29:22.440 Why? 414 29:22.920 --> 29:26.580 Because we are getting the correct correct result. 415 29:26.580 --> 29:28.500 Because here. 416 29:28.500 --> 29:32.790 Date name of year of higher date returns Nvarchar. 417 29:33.840 --> 29:40.920 This one will return the n where care and we are comparing it with integer 2021. 418 29:41.340 --> 29:50.750 We are comparing with integer two zero, two one and Invacare because of invacare has lower precedence 419 29:50.750 --> 29:52.460 than integer. 420 29:53.060 --> 29:56.330 So here in all data types. 421 29:59.930 --> 30:02.240 We will have some. 422 30:04.730 --> 30:07.010 Precedence are there. 423 30:07.500 --> 30:13.290 Suppose which data type we should give first. 424 30:13.290 --> 30:14.610 First preference. 425 30:14.670 --> 30:16.020 Precedence means. 426 30:18.510 --> 30:19.620 Preference. 427 30:19.620 --> 30:20.790 So integer. 428 30:20.790 --> 30:23.100 We are giving first preference or worker. 429 30:23.100 --> 30:25.830 We are giving first preference or date. 430 30:25.830 --> 30:28.950 We are giving first preference or decimal. 431 30:28.950 --> 30:30.600 We are giving first preference. 432 30:30.750 --> 30:40.850 So in this particular case, or anywhere, care has lower precedence lower preference than integer. 433 30:40.850 --> 30:44.300 So SQL server what SQL server will do? 434 30:44.960 --> 30:53.990 SQL server converts the inverse care into corresponding integer data data automatically. 435 30:56.200 --> 31:00.640 SQL server because of integer as higher precedence. 436 31:00.640 --> 31:07.780 Higher preference than the anywhere Care, which has lower precedence preference so that SQL server 437 31:07.780 --> 31:16.930 will convert the lower preference, which is anywhere care into higher preference, which is integer 438 31:16.930 --> 31:18.040 automatically. 439 31:18.760 --> 31:21.950 That is why we are getting the correct result. 440 31:22.160 --> 31:25.250 Hence we are getting the correct result. 441 31:25.910 --> 31:32.120 But if you mention over anywhere care right then also it will be fine. 442 31:32.120 --> 31:39.230 If you mention in the single quote, then also it will mention in this particular case only in this 443 31:39.230 --> 31:40.160 particular case. 444 31:40.160 --> 31:43.940 So in this case both thing thing will work. 445 31:43.970 --> 31:47.410 Now let's try whether this will work or not. 446 31:51.190 --> 31:51.700 Okay. 447 31:51.700 --> 31:58.780 In this particular scenario also it is automatically converting this thing into integer. 448 31:58.780 --> 32:03.430 It is automatically converting this thing into integer. 449 32:03.430 --> 32:08.990 So whether you write this or this it won't effect. 450 32:13.160 --> 32:19.550 Now let's see more examples of precedence so you can understand better. 451 32:20.780 --> 32:22.970 So now let me tell you. 452 32:23.660 --> 32:26.330 For example here we are giving one. 453 32:27.080 --> 32:32.720 So this is the integer where it is because it is not in the single code. 454 32:32.720 --> 32:32.740 quote. 455 32:32.740 --> 32:34.690 So that is why it is integer. 456 32:34.990 --> 32:41.140 And here it is one quote one means one in single quote one. 457 32:41.140 --> 32:43.420 So this is nvarchar. 458 32:43.780 --> 32:45.910 So over here what will we do. 459 32:46.180 --> 32:47.590 We will return two. 460 32:47.590 --> 32:49.000 This one will return two. 461 32:49.000 --> 32:49.540 Why. 462 32:49.810 --> 32:52.840 Because one will be convert this one. 463 32:53.710 --> 33:01.260 This one will be converted to integer as integer is higher precedence than worker or an worker. 464 33:01.800 --> 33:05.070 So that is why it will giving me two. 465 33:08.310 --> 33:10.440 That is why it will giving me two. 466 33:10.470 --> 33:12.930 Now let's see one more example. 467 33:22.480 --> 33:32.380 So over here it will be considering both of the string, both of the string as an vector. 468 33:33.430 --> 33:41.500 So when there will be the possibility of more than one string as a n vector at that time, it will not 469 33:41.500 --> 33:43.300 give us 24. 470 33:43.570 --> 33:45.570 It will not give us 24. 471 33:45.600 --> 33:49.050 It will suppose if it is a 23. 472 33:49.350 --> 33:49.650 Okay. 473 33:49.650 --> 33:51.450 It will be in the next example. 474 33:51.480 --> 34:00.060 So in this particular case, whenever the all of the parameters. 475 34:02.100 --> 34:09.050 Are in vector or any vector at that time. 476 34:09.050 --> 34:15.620 It will concat like two, three, one. 477 34:15.620 --> 34:20.390 So over here it will do two, three, one, not 24. 478 34:22.820 --> 34:31.760 For converting the value from anywhere care to automatically integer, we should have one value in integer 479 34:31.760 --> 34:34.830 we should have one value in integer data type. 480 34:34.920 --> 34:38.160 So over here it will not give me 231. 481 34:38.160 --> 34:39.750 It will give me 24. 482 34:42.420 --> 34:44.370 It will give me 24. 483 34:47.220 --> 34:55.350 Again this is integer and it will convert this to integer as integer as higher precedence. 484 34:56.540 --> 34:59.270 Now let me show you one more example. 485 34:59.660 --> 35:00.590 So in. 486 35:00.590 --> 35:03.710 Anyhow, a plus one is not possible, right? 487 35:03.950 --> 35:06.140 Because it is a integer value. 488 35:06.170 --> 35:08.240 How it will give me any result. 489 35:09.140 --> 35:13.850 Conversion failed when converting the vertical value a to data type integer. 490 35:16.460 --> 35:17.300 Right. 491 35:22.060 --> 35:23.770 But now suppose I have. 492 35:23.770 --> 35:24.670 Like this. 493 35:25.180 --> 35:26.920 Now let me try. 494 35:29.080 --> 35:30.160 A one right. 495 35:30.190 --> 35:32.380 So now what is it has done. 496 35:32.380 --> 35:34.960 It is converted both of the. 497 35:37.030 --> 35:40.330 Parameters or both of the values to invacare. 498 35:40.330 --> 35:42.070 And it is giving me the result. 499 35:45.500 --> 35:48.080 Now suppose my requirement. 500 35:48.080 --> 35:51.800 I want the result like this. 501 36:06.680 --> 36:09.400 Now suppose I want the result like this. 502 36:10.330 --> 36:17.080 Display a message in the MP table in front of every employee, for example. 503 36:17.500 --> 36:18.280 Smith. 504 36:18.280 --> 36:19.720 Join on Friday. 505 36:20.590 --> 36:20.950 Adam. 506 36:20.950 --> 36:21.910 Join on Monday. 507 36:21.910 --> 36:22.600 Like that. 508 36:22.600 --> 36:27.220 So over here I have this particular table. 509 36:27.220 --> 36:31.510 Select star from MP. 510 36:36.870 --> 36:45.750 So now over here in one of the extra column I want to mention like this employee name Smith joined on 511 36:46.590 --> 36:48.900 join on which date? 512 36:50.640 --> 36:51.390 Monday. 513 36:51.390 --> 36:52.020 Tuesday. 514 36:52.020 --> 36:52.590 Friday. 515 36:52.590 --> 36:53.340 Wednesday. 516 36:53.550 --> 37:00.430 So from that higher date, we can retrieve the particular date or date name. 517 37:00.430 --> 37:00.730 Right? 518 37:00.730 --> 37:03.310 Like Monday or Tuesday or Friday. 519 37:03.550 --> 37:08.560 So what we have to do select star, then Ename. 520 37:09.520 --> 37:12.640 Then I have retrieved this one column. 521 37:12.640 --> 37:15.280 Now again I want to retrieve one more column. 522 37:15.460 --> 37:18.280 So I have to concatenate concatenate. 523 37:18.280 --> 37:21.930 So we can do concatenate with plus. 524 37:22.440 --> 37:24.300 Then I have given one space. 525 37:24.990 --> 37:26.250 Then again plus. 526 37:26.250 --> 37:28.620 Then I have right right joined on. 527 37:29.280 --> 37:35.550 Then again plus then given one space, then over here joined on Friday or Monday. 528 37:35.550 --> 37:36.750 How can I take up. 529 37:37.320 --> 37:39.480 So for that I have to write date. 530 37:39.480 --> 37:39.690 Name. 531 37:39.690 --> 37:40.140 Function. 532 37:40.140 --> 37:40.440 Date. 533 37:40.440 --> 37:41.010 Name. 534 37:41.370 --> 37:42.450 Weekday. 535 37:42.990 --> 37:44.010 Higher date. 536 37:44.610 --> 37:45.470 So date. 537 37:45.470 --> 37:46.220 Name of week. 538 37:46.220 --> 37:46.520 Day. 539 37:46.520 --> 37:48.740 What it will give me, it will give me either. 540 37:48.740 --> 37:51.260 It is a fun day idea. 541 37:51.260 --> 37:55.490 Either it is a Friday Monday, Tuesday, Wednesday like that right. 542 38:00.620 --> 38:03.020 So let me see. 543 38:05.150 --> 38:06.350 The result set. 544 38:06.830 --> 38:10.710 Smith join on Wednesday Elon join on Friday. 545 38:11.310 --> 38:12.810 Adams join on Wednesday. 546 38:12.810 --> 38:18.330 So from this particular air date we have used the date name function. 547 38:18.540 --> 38:24.750 And from this date name function we have retrieved the higher date. 548 38:26.910 --> 38:27.780 Right.