SQL Server CONVERTData type conversion is a method used to convert the data type of a value into the other type. All programming languages must have some data type converting functions. Similarly, SQL Server allows the user to change the data type of a value either implicitly or explicitly. SQL Server performs implicit conversion for their internal needs. In contrast, database administrators or programmers perform the explicit conversion with the help of data conversion functions. This article will mainly focus on the use of a CONVERT function in Microsoft SQL Server. The CONVERT function enables the user to convert the data type of a value into another type specified in the expression. The syntax of a CONVERT function in SQL Server is given below: In the syntax, we have used the following parameters:
Return ValueIt will return a value of the expression in which data type we want to convert with a specified style. CONVERT Function ExampleLet us understand the CONVERT function with various practical examples. Convert FLOAT to INT This example converts the data type of float value to an integer. Here we have first declared a float variable and then assign them float value. Next, we will convert the float value to an integer with the help of the CONVERT function and print the result. After execution, we will get the below output: Convert Float to Varchar This example converts the data type of float value to varchar. It is similar to the above example, except here we will convert float value to varchar with the help of the CONVERT function. After execution, we will get the below output: Convert decimal to another decimal with different length This example will convert the decimal number into another decimal number with zero scales with the help of the CONVERT function. The rounding and truncation behavior of this function works similarly to the CAST function in SQL Server. After execution, we will get the below output: Convert String to DATETIME This example will convert the string '2021-04-26' to a DATETIME value with the help of the CONVERT function. After execution, we will get the below output: Convert DATETIME to Varchar This example will convert the current date and time to a string with a specific style using the CONVERT function. After execution, we will get the below output: Convert function with table Here we are going to see how to use the convert function in tables to filter the records. Let us first create a table "orders" using the below statements: We can verify the table using the SELECT statement: In the above table, the purchase_date is in DATE datatype. If we want to get the item list between selected ranges of date, we can use the below statement. Here is the output: TRY_CONVERT FunctionIt is the advanced form of the CONVERT function in SQL Server. It is useful in the data conversion process to protect from getting data converting errors during the query execution. With the CONVERT function, it's possible that we can get errors while converting data type into another form due to non-suitable or dirty data. But, these errors can be avoided by using the TRY_CONVERT function. The syntax of the CONVERT and TRY_CONVERT function have no differences. We will get the NULL result if we use the TRY_CONVERT function when the data conversion generates an error. Let us see the below example where we will try to convert a string value into an integer data type by using the CONVERT function: When we execute this query, SQL Server will raise the following error: However, SQL Server has a TRY_CONVERT function that protects from getting this type of error and returns the NULL value. See the below query: When we execute this query, SQL Server returns the NULL value instead of raising an error: Difference between CAST and CONVERT FunctionSQL Server always uses the CAST and CONVERT functions for data type conversion. However, they have some differences that we will be discussing below:
Next TopicSQL Server SELECT INTO
|