1 00:03.740 --> 00:05.510 Okay, let's let's start. 2 00:06.680 --> 00:08.450 Uh, today we will see. 3 00:10.280 --> 00:15.590 Today we will see the mathematical functions and conversion functions. 4 00:16.730 --> 00:19.340 So let's start with the very simple one. 5 00:19.340 --> 00:21.410 What is the absolute function. 6 00:21.410 --> 00:29.810 So absolute function will give if there is something like -20 -30 like then then it will change from 7 00:29.810 --> 00:34.640 -30 to positive 30 and positive 20. 8 00:34.970 --> 00:37.160 So let me run the query. 9 00:37.700 --> 00:40.010 Absolute of minus ten. 10 00:40.010 --> 00:41.390 So it will give me ten. 11 00:41.390 --> 00:47.180 As a result it will convert the negative number into positive number like that. 12 00:48.230 --> 00:49.910 Then let's see power one. 13 00:51.320 --> 00:53.090 Power function will give. 14 00:56.150 --> 01:06.920 Power of some particular number up to which uh, up to the parameter specify as a second parameter up 15 01:06.920 --> 01:09.350 to the value specified in the second parameter. 16 01:09.350 --> 01:15.290 So over here it will be three comma power two means nine three. 17 01:15.290 --> 01:16.400 Multiply by three nine. 18 01:16.400 --> 01:16.910 Right. 19 01:17.490 --> 01:19.110 and then four comma power three. 20 01:19.110 --> 01:20.490 So four multiply by four. 21 01:20.490 --> 01:21.630 Multiply by four. 22 01:21.990 --> 01:24.540 So for 64 right. 23 01:24.630 --> 01:28.560 So it will give me 64. 24 01:29.280 --> 01:30.570 Now square root. 25 01:32.370 --> 01:34.890 So returns the square root of given number. 26 01:34.890 --> 01:35.940 So 16. 27 01:35.940 --> 01:40.260 So square root of 1616 is four right. 28 01:40.770 --> 01:45.160 So it will give me four as a root. 29 01:47.230 --> 01:50.200 If you enter like 25 right. 30 01:50.200 --> 01:51.160 25. 31 01:51.160 --> 01:54.730 So it will give me five. 32 01:54.730 --> 01:55.000 Right. 33 01:55.000 --> 01:57.160 Five multiply by 525. 34 01:59.020 --> 02:00.010 Then what is the. 35 02:02.800 --> 02:04.090 Square function. 36 02:05.430 --> 02:07.680 So square function, it returns a number. 37 02:07.680 --> 02:15.540 It will return an integer value and it will return square of any particular number given by. 38 02:15.540 --> 02:16.020 So five. 39 02:16.020 --> 02:17.310 Multiply by five over here. 40 02:17.310 --> 02:21.030 Again it is 25 right. 41 02:21.540 --> 02:23.640 If you are given like ten. 42 02:24.150 --> 02:26.130 So ten multiply by ten. 43 02:26.130 --> 02:28.890 It is giving 100 right. 44 02:31.530 --> 02:33.450 And power of five comma two. 45 02:38.460 --> 02:41.400 So power is also giving me the same result. 46 02:41.400 --> 02:47.880 Whatever we will give in the second parameter, it will give me the power of some particular value up 47 02:47.880 --> 02:53.490 to whatever value has been given in the second parameter. 48 02:58.920 --> 03:06.120 And this is very interesting function and we are using it many times. 49 03:15.270 --> 03:17.010 We are using this function many times. 50 03:17.010 --> 03:20.800 So here what we have done over here. 51 03:20.800 --> 03:23.140 Let me remove this cache for now. 52 03:29.560 --> 03:34.150 So round of 38.478522. 53 03:34.720 --> 03:35.260 So. 54 03:39.280 --> 03:49.390 So it will give me 38.48, because whatever it is up to which decimal point you want to do round. 55 03:49.780 --> 03:52.810 So I want to do round up to two decimal places. 56 03:52.810 --> 03:53.350 Right. 57 03:56.110 --> 03:58.870 Up to two decimal places I want to do round. 58 04:02.050 --> 04:11.650 Now 47.85 means After 47, if it is greater than five, right? 59 04:11.650 --> 04:17.080 Greater than five, then it will convert this particular value to plus one. 60 04:18.340 --> 04:27.970 If this digit is greater than five, then it will convert this particular value to plus one. 61 04:39.370 --> 04:44.680 So it will give me 3038 .48. 62 04:45.190 --> 04:48.340 For now, forget this zero zero at the last. 63 04:52.420 --> 04:57.580 If you remove that zero zero then you can use the cast function over here. 64 04:57.580 --> 04:58.120 Cast. 65 04:58.120 --> 04:59.500 Let me try this one. 66 04:59.500 --> 05:00.190 Cast. 67 05:03.490 --> 05:05.200 Decimal five. 68 05:05.200 --> 05:06.130 Comma two. 69 05:12.430 --> 05:13.840 It is removed, right? 70 05:15.970 --> 05:16.990 It is removed. 71 05:16.990 --> 05:17.230 It. 72 05:17.320 --> 05:19.550 Last 200. 73 05:19.550 --> 05:19.790 Right. 74 05:19.790 --> 05:22.670 Which was previously coming like this. 75 05:31.760 --> 05:36.650 Now see this one round of 38.40 735 comma two. 76 05:37.250 --> 05:39.140 So over here if you see. 77 05:41.240 --> 05:42.290 This one is. 78 05:45.770 --> 05:48.980 This one is over here. 79 05:48.980 --> 05:52.760 This one is 30, uh, 38.47 three. 80 05:52.850 --> 06:01.340 So you have to check this digit because of we are doing round up to two decimal places. 81 06:05.540 --> 06:06.650 So 40. 82 06:06.950 --> 06:08.690 38.47. 83 06:08.690 --> 06:12.710 So it will remain 38.47 only. 84 06:19.460 --> 06:22.280 It will give me 38.47 only. 85 06:22.280 --> 06:22.610 Right. 86 06:22.610 --> 06:27.380 Because we are over here it is a not greater than five digit right. 87 06:29.510 --> 06:31.520 Now let's see for this one. 88 06:36.770 --> 06:40.520 Now here 38.47 351. 89 06:40.520 --> 06:42.830 We are doing now up to one only right. 90 06:43.700 --> 06:45.560 So we are doing up to one only. 91 06:45.560 --> 06:48.860 Then we have to see this decimal point. 92 06:50.270 --> 06:52.220 Now it is greater than five right. 93 06:52.220 --> 06:55.040 So it will convert this four into five. 94 06:56.810 --> 06:57.680 plus one right. 95 06:57.680 --> 06:59.840 So let me execute this one. 96 07:03.020 --> 07:08.330 38.5 right now again this uh round. 97 07:09.380 --> 07:11.030 Now it is giving over here. 98 07:11.030 --> 07:11.390 Zero. 99 07:11.390 --> 07:11.930 Right. 100 07:12.620 --> 07:16.220 So it will be 38 only because it is not greater than five. 101 07:16.220 --> 07:16.640 Right. 102 07:16.640 --> 07:21.960 This digit is not greater than five, so it will be 38 only. 103 07:21.960 --> 07:22.470 Right? 104 07:24.660 --> 07:28.440 Then again see for this one again zero. 105 07:28.440 --> 07:28.710 Right. 106 07:28.710 --> 07:31.350 We have to first see second parameter. 107 07:31.920 --> 07:33.540 Then we have to go to. 108 07:33.540 --> 07:36.030 If it is zero then we have to go to here. 109 07:36.030 --> 07:38.520 If it is one then we have to go to here. 110 07:39.030 --> 07:42.540 If it is two then we have to check here like that. 111 07:42.540 --> 07:43.320 So here zero. 112 07:43.320 --> 07:43.920 All right. 113 07:43.920 --> 07:44.820 So five. 114 07:44.820 --> 07:46.860 So five or greater than five. 115 07:46.860 --> 07:50.430 It will convert to this one to plus one. 116 07:50.430 --> 07:51.090 Right. 117 07:51.450 --> 07:53.580 So now it will give me 39. 118 07:54.960 --> 07:59.700 It is giving me 39 right now let's see. 119 07:59.700 --> 08:00.360 Minus one. 120 08:00.360 --> 08:00.630 Right. 121 08:00.630 --> 08:03.120 So minus one we will go into. 122 08:03.120 --> 08:05.790 Now reverse point from here point zero. 123 08:05.790 --> 08:08.520 Now here we are have to go to reverse point. 124 08:09.030 --> 08:10.800 Now this is three right. 125 08:11.040 --> 08:15.270 So it will convert this three to nearest integer value. 126 08:15.450 --> 08:18.570 So what is the nearest integer value of three. 127 08:18.990 --> 08:21.270 It is 380 right. 128 08:23.280 --> 08:27.210 For this three nearest integer value is zero. 129 08:27.210 --> 08:32.250 So it will convert this 383 383 into 380. 130 08:36.120 --> 08:38.400 Then let's see for this one. 131 08:38.400 --> 08:40.680 Now it is over here 386. 132 08:40.680 --> 08:42.840 So again we have to see this digit. 133 08:43.680 --> 08:45.900 This digit is greater than five right. 134 08:45.900 --> 08:47.940 This digit is greater than five. 135 08:48.480 --> 08:51.000 So we have to see 390. 136 08:51.000 --> 08:51.000 T. 137 08:56.490 --> 09:00.840 Greater than five it will convert to nearest integer value. 138 09:00.840 --> 09:02.910 Nearest integer value is 390. 139 09:02.940 --> 09:06.570 Right now here over here minus two has been done. 140 09:06.570 --> 09:10.830 So what we have to do we have to show see both of these. 141 09:10.830 --> 09:11.820 This digit. 142 09:12.420 --> 09:16.020 What is the nearest integer value of 383. 143 09:17.340 --> 09:18.990 It is 400 right. 144 09:18.990 --> 09:20.440 It is 400. 145 09:22.060 --> 09:27.730 400 is nearest value for this particular value. 146 09:27.730 --> 09:29.590 So it will give me 400. 147 09:31.900 --> 09:33.070 Now let's see. 148 09:33.100 --> 09:34.030 For this one. 149 09:34.270 --> 09:35.530 So for this one. 150 09:36.100 --> 09:38.920 325 what is the nearest integer value. 151 09:38.950 --> 09:40.390 It is 300 right. 152 09:42.550 --> 09:44.680 So it will give me 300. 153 09:49.690 --> 09:50.560 Ten minus three. 154 09:50.590 --> 09:53.590 We are doing of 325 minus three. 155 09:53.710 --> 09:54.610 So? 156 09:54.970 --> 09:56.710 So over here. 157 09:56.710 --> 10:00.730 It will give me zero because it will not give me 300. 158 10:00.730 --> 10:01.570 Because 300. 159 10:01.570 --> 10:05.440 It is giving me over here because of minus two are there. 160 10:05.440 --> 10:07.720 But over here minus three are there. 161 10:07.930 --> 10:12.760 So we are all three digit we are considering and it will give me zero. 162 10:18.490 --> 10:20.800 And over here it will give me 1000. 163 10:20.800 --> 10:21.130 Why. 164 10:21.130 --> 10:22.510 Because 500. 165 10:23.170 --> 10:24.730 It will check 525. 166 10:24.730 --> 10:29.230 So what is the nearest is actually 500. 167 10:29.230 --> 10:31.540 But it is greater than five right. 168 10:31.540 --> 10:32.020 It is. 169 10:32.020 --> 10:34.930 This one is starting from five or greater than five. 170 10:35.560 --> 10:38.170 So it will check greater than five. 171 10:38.170 --> 10:42.190 What is the nearest one up to this three digit. 172 10:42.190 --> 10:44.290 So it will give me directly. 173 10:47.140 --> 10:48.490 1000. 174 10:51.520 --> 11:01.870 Now let's see last example of round function and which is over here which is round of minus three. 175 11:01.870 --> 11:02.800 We are doing over here. 176 11:02.800 --> 11:03.310 Right. 177 11:03.790 --> 11:05.290 Round of minus three. 178 11:05.290 --> 11:09.280 So again over here it will give me. 179 11:10.570 --> 11:15.820 It will check here 1525. 180 11:15.820 --> 11:17.410 So what is the nearest one. 181 11:17.770 --> 11:19.780 It will not check 1500. 182 11:19.780 --> 11:24.820 It will check five is greater than or equal to five. 183 11:24.820 --> 11:25.060 Right. 184 11:25.060 --> 11:27.370 So it will directly convert this one. 185 11:28.900 --> 11:33.400 This number two plus one and all this will be zero. 186 11:33.400 --> 11:35.230 So it will give me 2000. 187 11:40.180 --> 11:42.040 This will give me two 2000. 188 11:42.040 --> 11:50.300 This may be used in your analytical part when you are doing some calculations or into already available 189 11:50.300 --> 11:57.980 columns, and you want to show some data here like yearly wise or quarterly wise, doing some manipulation 190 11:57.980 --> 12:01.970 on it at that time we are using round function many times. 191 12:02.660 --> 12:04.940 Now let's see the ceiling function. 192 12:06.110 --> 12:07.580 So what ceiling will do? 193 12:07.580 --> 12:10.250 It returns the smallest integer value. 194 12:10.520 --> 12:12.200 Just read it once again. 195 12:12.530 --> 12:19.100 It returns the smallest integer value greater than or equal to a given number. 196 12:19.700 --> 12:26.690 So over here, what will be the smallest integer value greater than or equal to a given number. 197 12:26.780 --> 12:31.550 So 3.1 smallest integer value which is greater than three. 198 12:31.550 --> 12:32.900 Right greater than three. 199 12:33.050 --> 12:34.970 So eight will be four, right. 200 12:38.240 --> 12:41.630 So ceiling will give me four in this particular case. 201 12:42.290 --> 12:42.980 Now what. 202 12:42.980 --> 12:45.530 This one will give 3.5. 203 12:45.530 --> 12:47.090 It will also give me four. 204 12:51.530 --> 12:55.250 It will also give me four because you have to remember this thing. 205 12:55.580 --> 13:00.710 It returns smallest integer value greater than or equal to the given number. 206 13:02.450 --> 13:03.650 Now let's see. 207 13:03.680 --> 13:04.580 Floor function. 208 13:04.580 --> 13:11.480 It returns the largest integer value less than or equal to the given number less than or equal to. 209 13:11.720 --> 13:14.600 So over here in this term kind. 210 13:14.600 --> 13:16.280 This time it will return three. 211 13:16.730 --> 13:18.350 Floor function will return three. 212 13:18.350 --> 13:18.680 Why? 213 13:18.680 --> 13:20.120 Because it will. 214 13:21.380 --> 13:25.820 Whatever it will return, the value should be less than a given number, right? 215 13:25.820 --> 13:27.590 Should be less than given number. 216 13:28.100 --> 13:33.680 Now again, this one will give me three only because less than 3.9. 217 13:33.680 --> 13:34.610 We have to check. 218 13:35.780 --> 13:44.390 Right now we have to see one more thing, in which case floor and ceiling may give same result, in 219 13:44.390 --> 13:48.030 which case floor and ceiling will give same result. 220 13:48.480 --> 13:56.460 So when we are not specifying any parameter over here like -1 or 1 or sorry, not parameter, but when 221 13:57.150 --> 14:02.190 value is not in the digit like this decimal point, right? 222 14:02.520 --> 14:05.730 So at that time it will give me the same result. 223 14:05.790 --> 14:07.440 So see over here. 224 14:09.780 --> 14:11.430 Law of three and ceiling of three. 225 14:11.430 --> 14:13.020 Now both will give me three. 226 14:13.020 --> 14:13.740 Three only. 227 14:19.980 --> 14:30.870 Because at this time it is not able to identify from where we have to check whether we have to go to 228 14:30.870 --> 14:35.130 less than or equal to given number, or greater than or equal to a given number. 229 14:36.120 --> 14:43.080 It will not able to decide whenever there is a point, it will able to decide that we have to check 230 14:43.080 --> 14:48.690 greater than or equal to 3.1, or less than or equal to 3.1. 231 14:49.350 --> 14:55.020 But in this scenario it is not able to decide, so it will give us three at the same time. 232 14:58.440 --> 15:05.400 So these are the mathematical functions mainly, but very rarely we are using it not many times, but 233 15:05.400 --> 15:10.170 only round function square root, square absolute. 234 15:10.170 --> 15:12.510 This all functions are very rarely used. 235 15:13.200 --> 15:18.810 Mostly we are using round functions only and cast is a conversion function. 236 15:18.810 --> 15:22.470 So let's move to conversion function now. 237 15:26.640 --> 15:28.890 So what are the conversion functions. 238 15:29.340 --> 15:30.720 So basically. 239 15:34.860 --> 15:38.880 They are used to convert one data type into another data type right. 240 15:39.510 --> 15:44.070 Conversion functions are used to convert from one data type to another data type. 241 15:44.670 --> 15:49.750 Now we have cast, convert and pass conversion functions are there. 242 15:50.200 --> 15:53.110 Now if I want to check like this. 243 15:53.710 --> 15:54.730 Display. 244 15:55.240 --> 15:57.190 Display in EMP table. 245 16:00.970 --> 16:02.200 Like this. 246 16:02.410 --> 16:06.220 Devin earns 3000 display from the EMP table. 247 16:06.250 --> 16:08.350 We have to identify Naveen. 248 16:08.350 --> 16:10.630 Earns 3010. 249 16:10.630 --> 16:14.710 Something like, uh, clerk earns 5000. 250 16:14.710 --> 16:16.480 We want to display like this. 251 16:16.990 --> 16:18.280 So we have to do concat. 252 16:18.280 --> 16:18.850 Right? 253 16:19.120 --> 16:22.240 Means we are using the plus operator over here. 254 16:22.690 --> 16:28.000 But if you see this is a string, then this is again a string. 255 16:28.300 --> 16:29.590 But what is this? 256 16:29.590 --> 16:32.860 This is a integer or money data type value. 257 16:32.860 --> 16:33.340 Right. 258 16:33.730 --> 16:37.120 Integer or money salaries in integer or money. 259 16:37.870 --> 16:44.200 Then it will not able to see do the plus or do the concat between all these three values. 260 16:45.220 --> 16:47.920 It will give me some error like this. 261 16:49.420 --> 16:49.750 Error. 262 16:49.750 --> 16:52.540 Converting data type varchar to numeric. 263 16:59.590 --> 17:02.650 So it is not able to convert me from this. 264 17:03.970 --> 17:07.600 Either this work to numeric or this numeric to worker. 265 17:07.780 --> 17:09.610 So it will give me error like this. 266 17:09.610 --> 17:10.180 Right? 267 17:10.900 --> 17:13.990 Now what should I write to convert it? 268 17:15.190 --> 17:19.060 So to convert it I need to change it as a caste. 269 17:19.090 --> 17:20.410 Caste of salary. 270 17:21.220 --> 17:24.430 As n any worker of ten. 271 17:26.530 --> 17:27.250 Or anything. 272 17:27.250 --> 17:29.500 If I do not write this last check. 273 17:33.130 --> 17:34.870 It will give me the result, right? 274 17:34.870 --> 17:35.860 Like this. 275 17:37.570 --> 17:44.020 What earns 12,500 then something King earns like this. 276 17:45.670 --> 17:48.470 Now how can I change it to convert? 277 17:48.500 --> 17:50.390 So let's see that one also. 278 17:51.080 --> 17:52.220 Select. 279 17:52.340 --> 17:53.990 Let me do. 280 17:57.920 --> 18:03.830 Copy this one then convert. 281 18:03.830 --> 18:07.370 Over here you have to write work anywhere care. 282 18:10.400 --> 18:11.930 You can write Max also. 283 18:11.930 --> 18:12.650 Max. 284 18:15.710 --> 18:16.490 Ten comma. 285 18:16.490 --> 18:18.710 Salary is. 286 18:25.760 --> 18:27.680 Now some problem is there. 287 18:27.680 --> 18:28.580 Let's see. 288 18:28.880 --> 18:30.440 Okay, now no problem. 289 18:30.890 --> 18:33.080 Now let's see the result of this one. 290 18:35.270 --> 18:38.660 It is also giving me the same thing which has been given over here. 291 18:38.660 --> 18:39.200 Right. 292 18:41.690 --> 18:47.900 Both of these in the first one I have used the cast and in the second one I have used the convert. 293 18:48.650 --> 18:56.450 Now there is some difference between cast and convert that we will see in the coming five minutes. 294 18:57.830 --> 18:59.930 Now let's see a few more examples. 295 19:01.730 --> 19:04.730 Now cast of 10.5 is integer. 296 19:05.540 --> 19:10.460 What it will do it is trying to convert ten fold five into integer. 297 19:11.150 --> 19:13.190 So let me try to do. 298 19:13.550 --> 19:15.590 So it will give me ten. 299 19:15.590 --> 19:16.190 Why? 300 19:17.750 --> 19:24.500 Because from this one the integer 10.5 is not an integer value. 301 19:24.500 --> 19:25.610 First of all, right. 302 19:26.000 --> 19:28.820 It will be a float or decimal value. 303 19:29.900 --> 19:36.200 So it will cast this particular value to integer automatically. 304 19:36.200 --> 19:42.740 And it will eliminate this thing which is described after a point. 305 19:43.820 --> 19:46.880 Suppose if you mention over here like 10.7. 306 19:47.910 --> 19:53.580 then also it will give me ten only not 11 right. 307 19:57.690 --> 19:59.430 Now let's move ahead. 308 19:59.760 --> 20:03.930 Now let's try what this will do. 309 20:04.560 --> 20:09.900 Select cos of 10.5 as integer plus one. 310 20:12.120 --> 20:15.870 So now this will give me 11.5. 311 20:15.870 --> 20:16.380 Why? 312 20:17.190 --> 20:19.050 Because of this one. 313 20:23.970 --> 20:27.510 This one will be automatically. 314 20:30.120 --> 20:31.080 Integer. 315 20:31.080 --> 20:35.790 From where care by SQL server. 316 20:38.040 --> 20:42.450 So 10.5 plus one. 317 20:42.450 --> 20:45.900 It will not give me 11.5 but it will. 318 20:45.900 --> 20:47.130 It should give me 11. 319 20:47.130 --> 20:48.090 Let me try. 320 20:48.120 --> 20:51.120 What it will give right. 321 20:51.120 --> 20:53.640 It will give me only 11 not 11.5. 322 20:53.640 --> 20:54.180 Right. 323 20:58.110 --> 21:03.630 Because from here it will consider only ten and ten plus 111, right? 324 21:07.050 --> 21:08.940 Now let's see this one. 325 21:09.330 --> 21:13.170 So now over here this 10.5 is over here as well. 326 21:13.260 --> 21:13.680 Here. 327 21:16.380 --> 21:17.700 It is not in integer. 328 21:17.700 --> 21:18.780 It is in vector. 329 21:19.440 --> 21:22.290 So what it this time what it will do. 330 21:22.290 --> 21:23.190 It will do. 331 21:24.450 --> 21:25.920 10.5 332 21:27.660 --> 21:29.670 plus one. 333 21:29.850 --> 21:32.730 But here both the values are in vertical. 334 21:35.280 --> 21:36.540 So it will do. 335 21:38.880 --> 21:41.430 10.51. 336 21:42.300 --> 21:43.320 Let's execute. 337 21:43.320 --> 21:44.550 Execute this one. 338 21:47.040 --> 21:49.630 It will give me 10.51. 339 21:50.320 --> 21:54.430 Now let's move ahead again over here. 340 21:54.670 --> 21:56.920 It is giving me the 10.51. 341 21:56.980 --> 21:58.270 What is the difference? 342 21:58.270 --> 22:00.970 Over here we have given vector of some number. 343 22:03.370 --> 22:06.010 Over here we have not given a vector of some number. 344 22:06.010 --> 22:11.890 Over here we have get given the specified the uh data type size. 345 22:12.340 --> 22:14.170 But over here we haven't specified. 346 22:14.170 --> 22:19.390 So both will for now, both will behave as the same way. 347 22:21.460 --> 22:23.020 Now let's see. 348 22:25.480 --> 22:29.950 Now over here it is trying to convert 10.5 S integer. 349 22:33.070 --> 22:34.690 Over here it it has converted. 350 22:34.690 --> 22:35.230 Why? 351 22:36.010 --> 22:39.340 Because over here it will be automatically in. 352 22:39.670 --> 22:41.230 Not as a worker. 353 22:41.230 --> 22:45.850 So if I write like this, it will give me some error. 354 22:47.800 --> 22:49.360 Same for over here. 355 22:49.360 --> 22:50.830 It will give me some error. 356 22:56.530 --> 22:58.600 Conversion failed when converting. 357 22:58.660 --> 23:00.010 Let us see this error. 358 23:00.370 --> 23:07.660 Conversion failed when converting the square value 10.5 to data type integer because over here this 359 23:07.660 --> 23:09.220 is known as a worker value. 360 23:09.220 --> 23:09.730 Why? 361 23:10.210 --> 23:12.640 Because it is given in the single quote. 362 23:14.320 --> 23:16.390 Because it is given in the single quote. 363 23:16.390 --> 23:24.040 That is why it is a worker value and from worker value it is trying to convert into integer so that 364 23:24.040 --> 23:25.510 it is giving me error. 365 23:28.960 --> 23:33.370 Now let's try 10.00 as integer. 366 23:34.450 --> 23:36.820 Okay, it will also give me the same thing. 367 23:37.180 --> 23:39.460 Now ten is integer. 368 23:40.720 --> 23:43.780 Now see over here it will give me result. 369 23:43.780 --> 23:44.290 Why? 370 23:45.250 --> 23:50.270 Because of there is no point and there is no any digit after point. 371 23:54.050 --> 24:05.690 So it will be easily able to convert from this vector value to higher precision integer value. 372 24:08.540 --> 24:11.450 It will able to convert, so it will return me ten. 373 24:17.090 --> 24:23.360 Now see this one 10.25 is decimal of seven comma two. 374 24:23.390 --> 24:26.270 So now over again it will able to convert y. 375 24:28.580 --> 24:36.170 Let me show you because if I write over here integer then it will not able to convert right. 376 24:40.070 --> 24:41.450 Then it will not able to convert. 377 24:41.450 --> 24:42.890 It will give me some error. 378 24:43.340 --> 24:45.500 Now let me try to write float. 379 24:48.710 --> 24:50.240 Now let me try. 380 24:50.270 --> 24:52.310 It should able to convert right. 381 24:52.820 --> 24:57.530 Because float and decimal consists the digits after point. 382 24:57.530 --> 24:58.100 Right. 383 24:58.130 --> 25:05.510 Because float and decimal both of the thing will be able to convert decimal after point so that it is 384 25:05.510 --> 25:16.160 not giving me error, but integer will give me only not able to considering after point right integer 385 25:16.160 --> 25:17.630 will not able to consider. 386 25:18.230 --> 25:23.120 So both of these thing will able to succeed but above. 387 25:25.160 --> 25:27.500 This one will not able to succeed. 388 25:28.070 --> 25:28.940 Not this one. 389 25:28.940 --> 25:31.490 When we specify over here as integer. 390 25:31.490 --> 25:32.000 Right. 391 25:34.790 --> 25:37.790 Now this is the same example we have seen. 392 25:38.210 --> 25:39.380 Salary. 393 25:40.310 --> 25:42.980 Someone's salary is like this. 394 25:44.720 --> 25:51.750 So over here in the message column I want to display like this that returns 5000. 395 25:51.870 --> 25:54.240 Smith earns 800 like this. 396 25:54.240 --> 25:56.700 So I can give this one. 397 25:56.700 --> 25:58.500 And then I write the update statement. 398 25:58.500 --> 25:59.010 Right. 399 26:02.400 --> 26:05.250 Now let's see the same thing we have seen. 400 26:05.250 --> 26:10.560 We can okay over here what we have done over here. 401 26:10.560 --> 26:16.830 We have done in place of in place of a plus one, we have used the concat function. 402 26:18.000 --> 26:24.570 And now when we are using the concat, it converts other data types into string or. 403 26:30.360 --> 26:32.370 Or any vector automatically. 404 26:32.400 --> 26:41.580 So that here no need to use cast or converting salary column so that no need to use cast and convert 405 26:41.580 --> 26:46.680 in salary column because it is automatically convert into string or worker. 406 26:46.680 --> 26:50.340 So let me show you this result okay. 407 26:50.340 --> 26:51.390 In the same result right. 408 26:51.390 --> 26:53.490 With the help of concrete function. 409 26:54.480 --> 26:57.210 So what concrete will do. 410 26:57.210 --> 26:58.110 It will do. 411 27:02.940 --> 27:04.500 Just like this. 412 27:05.550 --> 27:07.740 It will concrete all the numbers. 413 27:08.370 --> 27:10.050 All the string. 414 27:12.300 --> 27:13.170 Now. 415 27:13.170 --> 27:17.490 Okay, so now over here we need to display like this. 416 27:18.210 --> 27:21.900 Don't show it on some particular date as manager. 417 27:22.590 --> 27:24.090 So what is Jones? 418 27:24.750 --> 27:26.070 It's a upper name. 419 27:26.070 --> 27:27.570 E name is upper name. 420 27:27.570 --> 27:28.110 Right? 421 27:28.530 --> 27:30.000 All characters are in upper. 422 27:30.360 --> 27:31.680 So E name of upper. 423 27:31.680 --> 27:34.320 I have done then joined on. 424 27:34.320 --> 27:36.420 So plus join on. 425 27:36.420 --> 27:38.820 I have done right on. 426 27:38.820 --> 27:44.670 Then I have to convert this higher date from date column to vector, right? 427 27:45.000 --> 27:51.550 Then only I will able to concat the cast of higher date as n where care of ten t. 428 27:53.710 --> 27:57.250 And then plus s and then plus job. 429 27:57.250 --> 27:58.030 What is this? 430 27:58.030 --> 27:58.750 Manager. 431 27:59.110 --> 28:00.250 Manager. 432 28:00.640 --> 28:01.900 Clerk. 433 28:04.030 --> 28:05.020 Salesman. 434 28:05.020 --> 28:06.010 All are job. 435 28:07.930 --> 28:09.940 So job is manager. 436 28:10.210 --> 28:11.260 So over here. 437 28:11.260 --> 28:12.400 It will show me. 438 28:13.870 --> 28:19.180 He joined on August 6th 2024 as manager. 439 28:19.390 --> 28:24.880 Then this bond joined on February this as salesman. 440 28:26.200 --> 28:26.980 Right. 441 28:27.250 --> 28:28.900 So now let's move ahead. 442 28:29.530 --> 28:31.720 Now let's see the convert function. 443 28:34.960 --> 28:37.120 Now let's see the convert function. 444 28:37.630 --> 28:39.040 So what convert will do. 445 28:39.070 --> 28:42.220 Convert will also do the just same as cast. 446 28:42.970 --> 28:46.150 So over here this 10.5 is integer. 447 28:46.150 --> 28:48.940 Right now convert of workers. 448 28:48.940 --> 28:51.130 So it will convert this into worker. 449 28:51.310 --> 28:54.220 So 10.5 plus one. 450 28:54.220 --> 28:55.990 So 10.51 right. 451 28:55.990 --> 29:00.820 Convert will it will show me 10.51 right. 452 29:02.770 --> 29:03.250 now. 453 29:03.250 --> 29:04.000 Let's move. 454 29:05.050 --> 29:08.530 Does it able to convert this 10.5 into integer? 455 29:08.560 --> 29:09.160 No. 456 29:09.160 --> 29:09.880 Right. 457 29:10.420 --> 29:13.360 It will not able to convert 10.5 into integer. 458 29:13.360 --> 29:13.840 Why? 459 29:14.260 --> 29:18.130 Because conversion failed when converting 10.5 to data type integer. 460 29:18.970 --> 29:20.770 Now over here let's see. 461 29:23.140 --> 29:26.620 The same example we have seen two times that same example. 462 29:26.620 --> 29:31.510 Now we are saying is convert with some extra examples also. 463 29:31.510 --> 29:33.520 So again this will give me error. 464 29:34.120 --> 29:42.730 Now this will be successfully able to do commit integer ten because this is already in integer right. 465 29:43.120 --> 29:48.610 Actually over here it is in n square but after point no digits right. 466 29:48.610 --> 29:52.280 So it will easily able to identify. 467 29:52.310 --> 29:58.280 Now this will give me convert decimal of seven comma two. 468 29:58.820 --> 29:59.510 And then. 469 29:59.510 --> 30:01.910 So it will give me 10.25. 470 30:01.910 --> 30:02.420 Right. 471 30:06.470 --> 30:09.140 And again we have seen this example already. 472 30:09.650 --> 30:17.330 Now over here at the time of convert, we are able to write some extra parameters. 473 30:17.990 --> 30:27.680 So over here what it will do, it will convert this specific specific date into some parameter. 474 30:27.680 --> 30:30.860 So let me show you this link. 475 30:31.100 --> 30:34.640 Let me copy. 476 30:46.400 --> 30:47.990 Cost and convert. 477 30:48.470 --> 30:52.640 Okay, now let's see what I have mentioned over here. 478 30:52.640 --> 30:54.500 I have mentioned over here 101. 479 30:54.830 --> 30:56.390 So I have copied that one. 480 30:56.390 --> 31:01.970 And I have mentioned like MMD why why why why it is showing over here right. 481 31:02.390 --> 31:05.030 So let me go through over here. 482 31:07.280 --> 31:09.500 Let me show what it is showing. 483 31:16.010 --> 31:16.400 Okay. 484 31:16.400 --> 31:17.720 Convert of date. 485 31:21.440 --> 31:22.880 M m okay. 486 31:22.880 --> 31:24.950 This is considering as a February. 487 31:25.220 --> 31:27.710 Now let me try to enter like this. 488 31:30.110 --> 31:30.560 Okay. 489 31:30.560 --> 31:32.390 It is now giving me error right. 490 31:32.660 --> 31:33.440 Why. 491 31:33.920 --> 31:36.860 Because we have only 12 months are there. 492 31:36.860 --> 31:37.400 Right. 493 31:38.330 --> 31:41.990 Because we have only ten months or 12 months are there. 494 31:42.050 --> 31:48.020 So anything after 12, it will not accept anything. 495 31:48.020 --> 31:50.030 After 12 it will not accept. 496 31:50.550 --> 31:52.500 So I have to mention 12. 497 31:52.710 --> 31:54.840 Then it will give me a result like this. 498 31:58.980 --> 31:59.460 Over here. 499 31:59.460 --> 31:59.730 What? 500 31:59.730 --> 32:00.060 What? 501 32:00.060 --> 32:00.540 This one. 502 32:00.540 --> 32:01.740 How you can identify. 503 32:01.740 --> 32:03.090 You have to check this one. 504 32:07.710 --> 32:09.270 You have to copy this one. 505 32:09.930 --> 32:12.210 And I have copied over here. 506 32:15.630 --> 32:17.820 This is M, this is Rd. 507 32:18.360 --> 32:19.920 This is y y y y. 508 32:20.400 --> 32:30.030 And this is the first in which from string up from uh, marker value, I want to convert it into date. 509 32:30.030 --> 32:33.840 And I have to mention the date into this format. 510 32:34.110 --> 32:42.840 So how can I get this format, this format I have got from this internet and I have to mention like 511 32:42.870 --> 32:44.370 MD y y y y. 512 32:45.060 --> 32:52.530 So I have mentioned this m m d d y y y y and it will give me result like this. 513 32:52.860 --> 32:59.310 Now 103I have mentioned this facility is not provided when you are using Cast function. 514 33:00.270 --> 33:06.210 This facility is not provided when you are using cost function, but in convert it is provided. 515 33:06.210 --> 33:08.280 Now let's see what is 103? 516 33:08.850 --> 33:16.470 So 103 okay, over here I have to mention like this. 517 33:19.950 --> 33:25.260 Now over here can I mention like, uh, 32? 518 33:25.260 --> 33:26.280 Let me try. 519 33:29.700 --> 33:31.740 No, I am not able to mention 32. 520 33:31.740 --> 33:38.640 Right, because maximum date is 31, so it will accept 31, right? 521 33:39.060 --> 33:41.910 31st December 2016. 522 33:47.400 --> 33:51.060 It is giving me 31st December, 2016. 523 33:51.060 --> 33:51.600 Right? 524 33:53.190 --> 33:54.840 I like this format. 525 33:55.410 --> 33:57.480 Now let's move ahead. 526 33:58.080 --> 34:00.750 Displaying this in different formats. 527 34:01.470 --> 34:05.040 So over here we are displaying dates in different formats. 528 34:05.400 --> 34:10.950 Now over here I have mentioned 104104. 529 34:10.950 --> 34:12.060 What is 104. 530 34:12.060 --> 34:15.370 It is giving dd m Mhm. 531 34:15.370 --> 34:16.420 Why why why why. 532 34:17.620 --> 34:20.380 So now let me run this query. 533 34:25.630 --> 34:26.380 Okay. 534 34:26.380 --> 34:28.540 Now I am see over here. 535 34:29.080 --> 34:35.890 I am converting this higher date as a DM y y y y d d m m y y y y. 536 34:36.700 --> 34:39.580 So it is giving given over here, right d d m Mhm. 537 34:39.580 --> 34:40.390 Bye bye bye bye. 538 34:40.900 --> 34:43.630 So 104I checked 104. 539 34:44.080 --> 34:46.570 So it is giving me this value. 540 34:50.260 --> 34:53.260 And because of I have converted to wear care. 541 34:53.260 --> 34:55.810 Because of I have converted to wear care. 542 34:56.530 --> 35:00.430 It is giving me wear care of higher date and 104 format. 543 35:00.910 --> 35:04.000 So it will give me everything into this format. 544 35:04.780 --> 35:09.580 20 202 like this. 545 35:10.270 --> 35:13.360 But over here it is not giving me that format over here. 546 35:13.360 --> 35:16.240 It will give me always same format. 547 35:16.300 --> 35:23.530 But if I want to give like, uh worker then I have to write this one, this query. 548 35:27.310 --> 35:29.650 Right now let's see 106. 549 35:30.550 --> 35:33.820 So what is the formula of 106. 550 35:35.620 --> 35:40.000 Okay d m o n d m o n. 551 35:40.840 --> 35:42.880 You can't remember everything, right? 552 35:43.180 --> 35:44.380 What is 104? 553 35:44.380 --> 35:45.730 What is 103? 554 35:45.880 --> 35:52.690 You can't remember everything, so you can mention like this zero 4th April 1981. 555 35:52.690 --> 35:54.880 17th December 1981. 556 35:55.720 --> 35:57.970 17th November 1981. 557 35:57.970 --> 35:59.020 Like this. 558 35:59.260 --> 36:00.550 Now here it is. 559 36:00.550 --> 36:03.460 Giving me mom because of mom. 560 36:03.490 --> 36:05.680 It is giving me month three letters. 561 36:09.340 --> 36:14.950 Now let me try to convert this one cost of this. 562 36:14.980 --> 36:15.730 Okay. 563 36:16.460 --> 36:21.470 now over here we are trying to cast 10.0. 564 36:21.470 --> 36:23.060 Zero is integer right? 565 36:23.510 --> 36:25.130 So it is giving me error, right? 566 36:25.130 --> 36:30.320 Conversion failed when converting the vector value 10.002 data type integer. 567 36:30.980 --> 36:37.130 But now if I if I using parse this time it will give me some result. 568 36:38.330 --> 36:39.950 It is giving me ten. 569 36:40.430 --> 36:43.730 So to avoid this particular result set. 570 36:46.010 --> 36:47.990 It is giving me ten, right. 571 36:48.830 --> 36:51.650 So pass function what it will do. 572 36:51.650 --> 36:59.120 The SQL pass function is a SQL conversion function used to convert the string data to requested data. 573 36:59.120 --> 37:02.810 Type can take only string as first argument. 574 37:02.810 --> 37:05.360 Can only take string as first argument. 575 37:05.780 --> 37:09.890 If I write like this, let me try. 576 37:11.720 --> 37:16.820 Argument datatype numerics is invalid for argument one of pass function. 577 37:16.820 --> 37:17.360 Why? 578 37:18.380 --> 37:21.950 Because pass function will take only string argument. 579 37:21.980 --> 37:23.420 Now let me try this. 580 37:25.460 --> 37:27.080 Again same error right. 581 37:27.410 --> 37:33.080 So now you have to mention it as a string in the first argument. 582 37:33.650 --> 37:36.260 Then only it will give me correct result. 583 37:37.310 --> 37:41.150 And because of it is zero zero it is converted. 584 37:41.150 --> 37:43.280 It is eliminated this zero zero. 585 37:43.280 --> 37:50.120 Now let's see the same example over here 10.5 is integer. 586 37:50.630 --> 37:52.070 Now 10.5. 587 37:52.070 --> 37:54.080 How it can convert to integer. 588 37:54.080 --> 37:58.580 It cannot convert to integer even though we have used the parse function. 589 37:58.580 --> 37:59.180 Why? 590 37:59.600 --> 38:05.930 Because it cannot eliminate parse cannot eliminate 10.5 to 10. 591 38:07.520 --> 38:11.060 But if I use over here like cast. 592 38:12.110 --> 38:13.430 Now let me try. 593 38:16.440 --> 38:16.890 Okay. 594 38:16.890 --> 38:18.870 Cast is also will not be able to. 595 38:18.870 --> 38:21.240 We have to mention either over here zero. 596 38:22.290 --> 38:23.160 Then it will run. 597 38:23.160 --> 38:23.700 Run. 598 38:23.700 --> 38:25.860 Let me check okay. 599 38:28.050 --> 38:28.380 Okay. 600 38:28.380 --> 38:32.970 It will not run in the case of cast, but it will run in the case of parse. 601 38:35.970 --> 38:38.700 Right now let's see one more example. 602 38:39.420 --> 38:40.680 Ten is integer right. 603 38:40.710 --> 38:42.630 So ten is already in integer. 604 38:43.380 --> 38:45.720 Now 10.5 is decimal. 605 38:45.720 --> 38:49.830 So it will give me as decimal 10.00. 606 38:49.830 --> 38:53.100 It will give me error okay. 607 38:53.100 --> 38:53.490 Pass. 608 38:53.490 --> 38:54.510 It will work. 609 38:56.280 --> 38:58.650 Whether it is working is a cast or not. 610 38:58.650 --> 38:59.340 Let's see. 611 38:59.340 --> 39:04.350 I think we have seen that but I forget okay. 612 39:04.350 --> 39:06.120 In cast cost it is not working. 613 39:06.210 --> 39:08.250 But in pass it is working. 614 39:09.870 --> 39:11.070 Now let's see. 615 39:11.070 --> 39:12.780 This one is date. 616 39:14.460 --> 39:16.320 It is showing me a date. 617 39:16.320 --> 39:21.990 And Thursday, December 5th, 2019 is date time using a and us. 618 39:22.620 --> 39:27.960 So let me it is giving me something like this. 619 39:32.610 --> 39:34.500 Prefer using cast and convert. 620 39:34.500 --> 39:38.520 We are very rarely using this parse function. 621 39:38.520 --> 39:40.170 We are very rarely using. 622 39:40.830 --> 39:43.440 We are mostly using cast and convert. 623 39:49.080 --> 39:50.460 Now let's see. 624 39:53.160 --> 39:54.180 Few more examples. 625 39:54.180 --> 39:55.830 Then you can ask your questions. 626 39:55.830 --> 40:03.570 If you have parts of 10.5, parts of 10.5 is integer, right? 627 40:03.930 --> 40:08.160 So over here 10.5 it is not able to convert into integer. 628 40:08.160 --> 40:09.990 So it is giving me some error. 629 40:11.670 --> 40:12.030 Error. 630 40:12.030 --> 40:16.800 Converting string value 10.5 into data type integer using. 631 40:20.950 --> 40:21.820 Culture. 632 40:23.710 --> 40:29.230 Now if I try to do this 10.5 try pass. 633 40:29.260 --> 40:32.800 Now there is one more conversion function which is try pass. 634 40:33.700 --> 40:39.040 Then it will not able to even convert, but it will not give me any error. 635 40:40.240 --> 40:41.860 It will not give me any error. 636 40:41.860 --> 40:48.820 It will give me null when it is not able to convert, but when it is able to convert, it is giving 637 40:48.820 --> 40:51.790 me some value null and ten. 638 40:51.790 --> 40:56.680 So in the below case it will able to convert this ten into integer, right? 639 40:59.380 --> 41:01.960 Now let's see few more example. 640 41:05.320 --> 41:08.110 Again this cost 10.5 is integer. 641 41:08.110 --> 41:09.370 We have seen it already. 642 41:09.370 --> 41:18.010 It is not able to convert, but in try case let's say it will give me null. 643 41:18.340 --> 41:21.790 So like that try cast try nurse null. 644 41:22.780 --> 41:24.910 Sorry like to try pass try. 645 41:24.910 --> 41:29.140 Cast function is also there but very rarely we are using. 646 41:29.410 --> 41:31.090 Then let's see this one. 647 41:32.410 --> 41:34.480 This is obviously give me error right. 648 41:34.510 --> 41:38.230 A it will not able to convert into integer. 649 41:38.890 --> 41:41.020 So this will give me error. 650 41:41.440 --> 41:42.730 And this will give me null. 651 41:42.730 --> 41:43.360 Why. 652 41:44.020 --> 41:48.280 Because try function is basically used to. 653 41:50.410 --> 41:55.690 To eliminate the error which is throw which is thrown at the time of execution. 654 41:56.200 --> 42:02.230 So when it is not giving me error, either it will give me the correct result or it will give me null. 655 42:02.650 --> 42:06.610 So over here in this particular case, it is giving me null, right? 656 42:11.020 --> 42:13.720 Now convert integer a. 657 42:13.990 --> 42:18.110 So again here it is giving me error and try convert. 658 42:18.740 --> 42:22.550 So like try parse try cast. 659 42:22.580 --> 42:24.440 Try convert is also there. 660 42:24.950 --> 42:27.050 And again it will not throw me error. 661 42:27.050 --> 42:35.060 It will return me null whenever it is not able to convert from some data type to some other data type. 662 42:35.060 --> 42:36.440 So it will give me null. 663 42:40.940 --> 42:45.140 So we have seen this mathematical and conversion functions. 664 42:45.980 --> 42:48.410 This link is very important one. 665 42:52.910 --> 42:59.900 You can take it for your reference and you will see over here everything is given right. 666 43:02.540 --> 43:06.950 Now let me stop the recording.