TRY_CONVERT() Function

• The TRY_CONVERT() function is used for data conversions, that is, in converting one type of data to another type.
• The possible conversions range from int data type to varchar data type, varchar data type to int data type, and other similar conversions.
• If the conversion process fails to convert the input data types into the required data types, such failed conversions are represented as ‘NULL‘ values.

Prerequisites:

• SQL Server 2012.

• SQL Server Management Studio.

Table Creation

Suppose we have created a database called w3wikidb under which we will create a table called students which contains student_id, student_name, student_city, and student_dob as Columns.

The structure for Create a Table is Given Below.

Query:

CREATE TABLE students
(student_id INT,
student_name VARCHAR(32),
student_city VARCHAR(32),
student_dob VARCHAR(32)
PRIMARY KEY(student_id));

Let’s insert some data into our students Table for performing the operations.

Query:

INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES(10, 'robin','mumbai', '02-08-1994');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES(20, 'jack','delhi', '04-07-1992');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES(30, 'jane','chennai', '04-10-1995');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES(40 'john','mumbai', '07-08-1999');
INSERT INTO students(student_id,student_name,student_city,student_dob)
VALUES(50,'julie','mumbai', '01-06-1991');

Output:

This data insertion into the students table was done through the INSERT command of SQL Server. Now, after we have our data inserted into the students table, let us make us of the TRY_CONVERT function for certain examples.

Syntax:

TRY_CONVERT(data, dataType)

Explanation: Here data is the given column(student_dob) to be converted while the dataType specifies the required format into which the data must be converted.

When we deal with databases, we come across different data types. In SQL we have various data types to store different types of data. Like int data type for integers, varchar data type for strings, date data type for storing the data, and XML for XML type data.

For such types of data conversions, we have several functions provided by the SQL server to achieve this. We shall understand such a function which is the TRY_CONVERT() function with the help of a suitable example.

For our example, we need the SQL Server 2012 installed along with the SQL Server Management Studio workbench.