title
Cast and Convert functions in SQL Server Part 28

description
In this video we will learn, converting one data type to another using cast() and convert() functions. We will also learn how to use the style parameter, with convert() function. Finally we will look at a practical example of using CAST or CONVERT functions, along with group by. We will also talk about the differences between CAST and CONVERT functions and when to choose one over the other. Text version of the video http://csharp-video-tutorials.blogspot.com/2012/09/cast-and-convert-functions-in-sql.html Healthy diet is very important both for the body and mind. If you like Aarvi Kitchen recipes, please support by sharing, subscribing and liking our YouTube channel. Hope you can help. https://www.youtube.com/channel/UC7sEwIXM_YfAMyonQCrGfWA/?sub_confirmation=1 Slides http://csharp-video-tutorials.blogspot.com/2013/08/part-28-cast-and-convert-functions.html All SQL Server Text Articles http://csharp-video-tutorials.blogspot.com/p/free-sql-server-video-tutorials-for.html All SQL Server Slides http://csharp-video-tutorials.blogspot.com/p/sql-server.html All Dot Net and SQL Server Tutorials in English https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd All Dot Net and SQL Server Tutorials in Arabic https://www.youtube.com/c/KudvenkatArabic/playlists

detail
{'title': 'Cast and Convert functions in SQL Server Part 28', 'heatmap': [{'end': 439.576, 'start': 418.207, 'weight': 0.834}, {'end': 503.558, 'start': 471.09, 'weight': 0.936}, {'end': 869.221, 'start': 857.029, 'weight': 0.778}], 'summary': 'Explores the usage of cast and convert functions in sql server to convert data types, demonstrating syntax and usage through examples, achieving the desired output of a converted date of birth column with a three-letter month name and a.m./p.m. time format, discussing the optional length parameter and its impact, the difference between cast and convert, the usage of style parameter in convert, and the introduction of the date data type in sql server 2008.', 'chapters': [{'end': 142.681, 'segs': [{'end': 119.195, 'src': 'embed', 'start': 92.472, 'weight': 0, 'content': [{'end': 96.716, 'text': "So in order to convert the date of birth, I'm using the cast function.", 'start': 92.472, 'duration': 4.244}, {'end': 104.43, 'text': "OK So cast and I'm saying cast this date of birth to nvacar.", 'start': 96.736, 'duration': 7.694}, {'end': 113.393, 'text': 'Okay, so if you look at the syntax, cast expression or column name as your target data type.', 'start': 104.99, 'duration': 8.403}, {'end': 116.334, 'text': 'Our target data type here is nvarchar.', 'start': 113.733, 'duration': 2.601}, {'end': 119.195, 'text': 'And if you look at there, we have another piece to it.', 'start': 116.674, 'duration': 2.521}], 'summary': 'Converting date of birth using cast function to nvarchar.', 'duration': 26.723, 'max_score': 92.472, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a892472.jpg'}], 'start': 0.029, 'title': 'Utilizing sql server functions', 'summary': 'Explores the use of cast and convert functions in sql server to convert data types, demonstrating the syntax and usage through examples, achieving the desired output of a converted date of birth column with a three-letter month name and a.m./p.m. time format.', 'chapters': [{'end': 142.681, 'start': 0.029, 'title': 'Sql server: cast and convert functions', 'summary': 'Explores the use of cast and convert functions in sql server to convert data types, demonstrating the syntax and usage through examples, ultimately achieving the desired output of a converted date of birth column with a three-letter month name and a.m./p.m. time format.', 'duration': 142.652, 'highlights': ['The chapter explains the use of cast and convert functions in SQL Server to convert one data type to another, with a focus on achieving a specific output format for the date of birth column, using a three-letter month name and a.m./p.m. time format.', "The speaker provides a detailed explanation of the syntax for the cast function in SQL Server, demonstrating how to cast a date of birth to nvarchar, with optional specification of length, offering a comprehensive understanding of the function's usage."]}], 'duration': 142.652, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a829.jpg', 'highlights': ['The chapter explains the use of cast and convert functions in SQL Server to convert one data type to another, with a focus on achieving a specific output format for the date of birth column, using a three-letter month name and a.m./p.m. time format.', "The speaker provides a detailed explanation of the syntax for the cast function in SQL Server, demonstrating how to cast a date of birth to nvarchar, with optional specification of length, offering a comprehensive understanding of the function's usage."]}, {'end': 673.043, 'segs': [{'end': 194.586, 'src': 'embed', 'start': 165.03, 'weight': 4, 'content': [{'end': 168.31, 'text': 'so when I execute that, we should get the converted date of birth.', 'start': 165.03, 'duration': 3.28}, {'end': 173.772, 'text': 'okay, now here for the target data type, we are not specifying the length.', 'start': 168.31, 'duration': 5.462}, {'end': 175.612, 'text': 'if you wish, you can specify the length.', 'start': 173.772, 'duration': 1.84}, {'end': 183.534, 'text': 'for example, let us say if I say the length as 5, convert the date time, date of birth to n, where char of length 5.', 'start': 175.612, 'duration': 7.922}, {'end': 189.956, 'text': 'so obviously, when we execute this query, as you might expect, you know, the output will be knocked off to 5 characters.', 'start': 183.534, 'duration': 6.422}, {'end': 194.586, 'text': 'Okay, so you may want to be careful when you specify that length.', 'start': 190.962, 'duration': 3.624}], 'summary': 'Specifying the length for date conversion affects output length.', 'duration': 29.556, 'max_score': 165.03, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8165030.jpg'}, {'end': 230.247, 'src': 'embed', 'start': 204.865, 'weight': 1, 'content': [{'end': 210.029, 'text': 'So if you want time, maybe you will have to slightly increase that to maybe 20.', 'start': 204.865, 'duration': 5.164}, {'end': 211.91, 'text': 'Yep, we get the time as well.', 'start': 210.029, 'duration': 1.881}, {'end': 215.833, 'text': "Okay, now, so that's the syntax for cast function.", 'start': 212.451, 'duration': 3.382}, {'end': 223.339, 'text': 'Cast your source column as target data type and length of the target data type is optional.', 'start': 216.353, 'duration': 6.986}, {'end': 226.462, 'text': 'And the same is the case as convert function.', 'start': 224.64, 'duration': 1.822}, {'end': 230.247, 'text': 'If you look at the convert function, you specify the target data type first.', 'start': 226.482, 'duration': 3.765}], 'summary': 'Syntax for cast and convert functions, specify target data type.', 'duration': 25.382, 'max_score': 204.865, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8204865.jpg'}, {'end': 443.079, 'src': 'heatmap', 'start': 410.305, 'weight': 3, 'content': [{'end': 417.907, 'text': 'So, so far what we have seen, we have understood that there are two ways to convert from one data type to another, cast and convert.', 'start': 410.305, 'duration': 7.602}, {'end': 423.648, 'text': "What is the difference between them? Convert has an optional style parameter whereas cast doesn't have it.", 'start': 418.207, 'duration': 5.441}, {'end': 427.765, 'text': "We'll look at more differences between cast and convert in a bit.", 'start': 425.103, 'duration': 2.662}, {'end': 431.548, 'text': 'Also, we have seen how to read the MSDN documentation.', 'start': 428.886, 'duration': 2.662}, {'end': 437.974, 'text': "So, according to the MSDN documentation, if something isn't within the square brackets, then that piece is optional.", 'start': 431.909, 'duration': 6.065}, {'end': 439.576, 'text': 'You may or may not provide that.', 'start': 437.994, 'duration': 1.582}, {'end': 443.079, 'text': 'Just like length and style parameters.', 'start': 440.717, 'duration': 2.362}], 'summary': 'Two ways to convert data types: cast and convert, with differences in optional parameters.', 'duration': 32.774, 'max_score': 410.305, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8410305.jpg'}, {'end': 503.558, 'src': 'heatmap', 'start': 471.09, 'weight': 0.936, 'content': [{'end': 474.051, 'text': "Okay, so that's one way and that's exactly here.", 'start': 471.09, 'duration': 2.961}, {'end': 478.543, 'text': "That's one way to get just the date part of a complete date time.", 'start': 474.091, 'duration': 4.452}, {'end': 485.347, 'text': 'But in SQL Server 2008, we have this date data type introduced.', 'start': 480.364, 'duration': 4.983}, {'end': 488.529, 'text': 'In SQL Server 2005, we only have date time.', 'start': 485.627, 'duration': 2.902}, {'end': 498.315, 'text': 'But in SQL Server 2008, they have date time, date, as well as time, three different parameters, data types, sorry.', 'start': 489.75, 'duration': 8.565}, {'end': 503.558, 'text': 'So, since we have a whole, you know just the date data type.', 'start': 499.276, 'duration': 4.282}], 'summary': 'Sql server 2008 introduced date data type in addition to date time and time data types.', 'duration': 32.468, 'max_score': 471.09, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8471090.jpg'}, {'end': 514.104, 'src': 'embed', 'start': 480.364, 'weight': 0, 'content': [{'end': 485.347, 'text': 'But in SQL Server 2008, we have this date data type introduced.', 'start': 480.364, 'duration': 4.983}, {'end': 488.529, 'text': 'In SQL Server 2005, we only have date time.', 'start': 485.627, 'duration': 2.902}, {'end': 498.315, 'text': 'But in SQL Server 2008, they have date time, date, as well as time, three different parameters, data types, sorry.', 'start': 489.75, 'duration': 8.565}, {'end': 503.558, 'text': 'So, since we have a whole, you know just the date data type.', 'start': 499.276, 'duration': 4.282}, {'end': 514.104, 'text': 'what you can do is, instead of converting a date time data type to nvacar, you can say okay, I will convert it to just date,', 'start': 503.558, 'duration': 10.546}], 'summary': 'Sql server 2008 introduced new date data type, including date, time, and datetime, allowing easier conversions.', 'duration': 33.74, 'max_score': 480.364, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8480364.jpg'}, {'end': 556.212, 'src': 'embed', 'start': 528.171, 'weight': 5, 'content': [{'end': 533.861, 'text': 'you know you still have to convert the date time to nvacar using the styles provided.', 'start': 528.171, 'duration': 5.69}, {'end': 540.287, 'text': 'When converting to date data type, the convert function will ignore the style parameter.', 'start': 534.342, 'duration': 5.945}, {'end': 550.156, 'text': "The convert function will respect the style parameter only if you're converting it to varchar or nvarchar, basically to the string data type.", 'start': 541.688, 'duration': 8.468}, {'end': 556.212, 'text': "All right, so that's about how we want to convert, you know, date time just to date.", 'start': 551.53, 'duration': 4.682}], 'summary': 'Convert date time to nvarchar using provided styles.', 'duration': 28.041, 'max_score': 528.171, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8528171.jpg'}], 'start': 143.081, 'title': 'Sql data type conversion', 'summary': 'Discusses converting date of birth to nvarchar using the cast and convert functions, emphasizing the optional length parameter and its impact, as well as the difference between cast and convert, the usage of style parameter in convert, and the introduction of the date data type in sql server 2008.', 'chapters': [{'end': 249.587, 'start': 143.081, 'title': 'Converting date of birth to nvarchar', 'summary': 'Discusses converting date of birth to nvarchar using the cast and convert functions, emphasizing the optional length parameter and its impact on the output.', 'duration': 106.506, 'highlights': ['The syntax for cast function involves casting the source column to the target data type, with the optional length parameter, demonstrating the impact of specifying different lengths on the output.', 'Explaining the syntax for the convert function, which requires specifying the target data type followed by the source column, emphasizing the optional length parameter and its impact on the output.', 'Emphasizing the impact of specifying different lengths when converting date of birth to NVARCHAR using the cast and convert functions, highlighting the need to carefully consider the length parameter for desired output.']}, {'end': 673.043, 'start': 249.587, 'title': 'Sql server data type conversion', 'summary': 'Discusses the difference between cast and convert functions, the usage of style parameter in convert function to control the formatting of date and time, and the introduction of the date data type in sql server 2008.', 'duration': 423.456, 'highlights': ['The difference between CAST and CONVERT functions CAST does not have an optional style parameter, whereas CONVERT does. This allows CONVERT to control the formatting of date and time.', 'Usage of style parameter in CONVERT function The style parameter in CONVERT function allows for formatting date and time, and is indicated by an integer number. Various styles are available with 20 to 30 options listed on the MSDN documentation.', 'Introduction of the date data type in SQL Server 2008 In SQL Server 2008, the introduction of the date data type allowed for direct conversion of date time to just the date part, eliminating the need to use the style parameter for formatting the date part.']}], 'duration': 529.962, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8143081.jpg', 'highlights': ['The introduction of the date data type in SQL Server 2008 allowed for direct conversion of date time to just the date part, eliminating the need to use the style parameter for formatting the date part.', 'The syntax for the convert function requires specifying the target data type followed by the source column, emphasizing the optional length parameter and its impact on the output.', 'The syntax for the cast function involves casting the source column to the target data type, with the optional length parameter, demonstrating the impact of specifying different lengths on the output.', 'The difference between CAST and CONVERT functions: CAST does not have an optional style parameter, whereas CONVERT does, allowing CONVERT to control the formatting of date and time.', 'Emphasizing the impact of specifying different lengths when converting date of birth to NVARCHAR using the cast and convert functions, highlighting the need to carefully consider the length parameter for desired output.', 'Usage of style parameter in CONVERT function: The style parameter allows for formatting date and time, and is indicated by an integer number. Various styles are available with 20 to 30 options listed on the MSDN documentation.']}, {'end': 1044.567, 'segs': [{'end': 778.578, 'src': 'embed', 'start': 751.876, 'weight': 0, 'content': [{'end': 755.518, 'text': 'Okay? So, obviously, if we have to do this, we use group by class.', 'start': 751.876, 'duration': 3.642}, {'end': 762.769, 'text': "Again, if you're new to group by, please check the earlier you know parts in this video series.", 'start': 755.558, 'duration': 7.211}, {'end': 764.71, 'text': 'so now let us see how to write this query.', 'start': 762.769, 'duration': 1.941}, {'end': 768.732, 'text': "and obviously, if you look at this here, it's pretty simple in the output what you want.", 'start': 764.71, 'duration': 4.022}, {'end': 772.074, 'text': 'you want the registration date and total registrations.', 'start': 768.732, 'duration': 3.342}, {'end': 775.276, 'text': 'so we will select the registration date.', 'start': 772.074, 'duration': 3.202}, {'end': 778.578, 'text': "okay, let's first write this query.", 'start': 775.276, 'duration': 3.302}], 'summary': 'Use group by class to get registration date and total registrations.', 'duration': 26.702, 'max_score': 751.876, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8751876.jpg'}, {'end': 881.889, 'src': 'heatmap', 'start': 857.029, 'weight': 0.778, 'content': [{'end': 864.658, 'text': "Huh Okay, so let's execute that.", 'start': 857.029, 'duration': 7.629}, {'end': 869.221, 'text': 'Now, if you look at the output, this is not what we expected.', 'start': 865.078, 'duration': 4.143}, {'end': 878.007, 'text': 'Now, why did we get this output? Because you are saying group by registered date, but within the date, we have got date and time as well.', 'start': 869.701, 'duration': 8.306}, {'end': 881.889, 'text': 'At the same date and time, nobody has registered.', 'start': 878.527, 'duration': 3.362}], 'summary': 'Data output mismatch due to date and time in group by clause.', 'duration': 24.86, 'max_score': 857.029, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8857029.jpg'}, {'end': 921.237, 'src': 'embed', 'start': 891.832, 'weight': 1, 'content': [{'end': 898.853, 'text': 'okay, so if you want to group just by the date, then you will have to convert this to date data type.', 'start': 891.832, 'duration': 7.021}, {'end': 914.236, 'text': "so let's convert this to date data type, and to do that we can use cast register date, as we can use the date data type.", 'start': 898.853, 'duration': 15.383}, {'end': 921.237, 'text': "so we need to do the same thing even in the select list, because we don't want the time, we just want the date and we want to group by that date.", 'start': 914.236, 'duration': 7.001}], 'summary': 'Convert data to date type for grouping by date.', 'duration': 29.405, 'max_score': 891.832, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8891832.jpg'}, {'end': 1023.67, 'src': 'embed', 'start': 995.227, 'weight': 2, 'content': [{'end': 999.552, 'text': "because it's based on ANSI standard, whereas convert is specific to SQL Server.", 'start': 995.227, 'duration': 4.325}, {'end': 1005.416, 'text': 'But the advantage of using convert is that it provides more flexibility than cast,', 'start': 1000.813, 'duration': 4.603}, {'end': 1015.104, 'text': 'and we have seen examples where we have actually controlled how we want date time data types to be converted using styles with the convert function.', 'start': 1005.416, 'duration': 9.688}, {'end': 1020.088, 'text': 'With the cast function we cannot use style parameter.', 'start': 1016.144, 'duration': 3.944}, {'end': 1023.67, 'text': 'Now, when should you use one over the other?', 'start': 1020.588, 'duration': 3.082}], 'summary': 'Using convert provides more flexibility than cast, allowing control over date time data types through styles.', 'duration': 28.443, 'max_score': 995.227, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8995227.jpg'}], 'start': 673.443, 'title': 'Sql group by and conversion functions', 'summary': 'Focuses on using the group by clause to count registrations by date and compares cast and convert functions, advocating for the flexibility and control offered by the convert function over date time data types.', 'chapters': [{'end': 1044.567, 'start': 673.443, 'title': 'Sql group by and conversion functions', 'summary': 'Explains how to use the group by clause to count registrations by date and discusses the differences between cast and convert functions, including the advantage of using convert for more flexibility and control over date time data types.', 'duration': 371.124, 'highlights': ['Using group by to count registrations by date The chapter demonstrates how to write a query to count the total registrations by date using group by clause, showing the highest and lowest number of registrations on specific dates.', 'Converting date time data type using cast and group by The speaker explains the need to convert the date time data type to date data type using the cast function to correctly group the registrations by date, preventing incorrect grouping by date and time.', 'Differences between cast and convert functions The chapter discusses the differences between cast and convert functions, highlighting that cast is based on ANSI standard and can be used across different database applications, while convert provides more flexibility and control over date time data types using styles.']}], 'duration': 371.124, 'thumbnail': 'https://coursnap.oss-ap-southeast-1.aliyuncs.com/video-capture/8GHUfb5k-a8/pics/8GHUfb5k-a8673443.jpg', 'highlights': ['Using group by to count registrations by date The chapter demonstrates how to write a query to count the total registrations by date using group by clause, showing the highest and lowest number of registrations on specific dates.', 'Converting date time data type using cast and group by The speaker explains the need to convert the date time data type to date data type using the cast function to correctly group the registrations by date, preventing incorrect grouping by date and time.', 'Differences between cast and convert functions The chapter discusses the differences between cast and convert functions, highlighting that cast is based on ANSI standard and can be used across different database applications, while convert provides more flexibility and control over date time data types using styles.']}], 'highlights': ['The introduction of the date data type in SQL Server 2008 allowed for direct conversion of date time to just the date part, eliminating the need to use the style parameter for formatting the date part.', 'The syntax for the convert function requires specifying the target data type followed by the source column, emphasizing the optional length parameter and its impact on the output.', 'The syntax for the cast function involves casting the source column to the target data type, with the optional length parameter, demonstrating the impact of specifying different lengths on the output.', 'The difference between CAST and CONVERT functions: CAST does not have an optional style parameter, whereas CONVERT does, allowing CONVERT to control the formatting of date and time.', 'Emphasizing the impact of specifying different lengths when converting date of birth to NVARCHAR using the cast and convert functions, highlighting the need to carefully consider the length parameter for desired output.', 'Usage of style parameter in CONVERT function: The style parameter allows for formatting date and time, and is indicated by an integer number. Various styles are available with 20 to 30 options listed on the MSDN documentation.', 'Using group by to count registrations by date The chapter demonstrates how to write a query to count the total registrations by date using group by clause, showing the highest and lowest number of registrations on specific dates.', 'Converting date time data type using cast and group by The speaker explains the need to convert the date time data type to date data type using the cast function to correctly group the registrations by date, preventing incorrect grouping by date and time.', 'Differences between cast and convert functions The chapter discusses the differences between cast and convert functions, highlighting that cast is based on ANSI standard and can be used across different database applications, while convert provides more flexibility and control over date time data types using styles.']}