SQL Server TRY CONVERT() Function

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.

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 geeksforgeeksdb 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:

Students Table

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.

Examples of TRY_CONVERT Function

Example 1: Converting a VARCHAR Type to DATE Type

Query:

SELECT TRY_CONVERT(DATE,student_dob) as dob_in_date
FROM students;

Output:

Result

Explanation: As seen in the above query, The TRY_CONVERT() function takes the given input student_dob and then converts this input into the required format. Here, the input is being converted into the date data type format.

If the conversion is successful then the TRY_CONVERT() function returns the data converted into the given format or else it returns a NULL value.

Example 2: Converting an INT Type to a VARCHAR Type

Query:

SELECT TRY_CONVERT(VARCHAR,student_id) as student_id_in_varchar
FROM students;

Output:

The student_id column successfully converted into the varchar data type

Output

Explanation: As seen from the above query, we have successfully converted the student_id column from one data type to another data type, that is from Integer data type to Varchar data type.

Here, the input is being converted into the varchar data type format. If the conversion is successful the TRY_CONVERT() function returns the data converted into the given format or else it returns a NULL value.

Example 3: Converting a VARCHAR Type to an INT Type:

Query:

SELECT TRY_CONVERT(INT,student_name) as student_name_in_int
FROM students;

Output:

The student_name column successfully converted into the int data type

Output

Explanation: As seen from the above query, we have provided the student_name column as the input to the TRY_CONVERT() function. Using this function, we try to convert the student_name of varchar data type into the integer data type.

However, as we can see, from our inserted data in the students table, there exists no record that contains only plain numeric data. Hence, this results in a failed conversion using the TRY_CONVERT() function which is why we have NULL displayed as the result in our output.

Example 4: Converting a VARCHAR Type to a DATE Type Using CASE Expression

Now consider a use case where we have a voting system that will be introduced for the students. Each student has to cast their vote in this process. However, only those students will be eligible to cast their votes who are above 18 years of age.

As an example, let us consider that all those students that were born after the date ‘04-07-1992′ would be of 18 years of age & above. That is, those students whose date of birth lies after the given date would be eligible to cast their votes for this voting process. We require a list of these students records.

Each record must contain the students name if the particular student eligible to vote or else “Not Selected” should be displayed for those records where the student was ineligible to vote, along with the student’s city.

This is our given requirement where we need our output to consist of 2 columns namely:

• selected_students: Displaying the students name for all eligible students, while displaying “Not Selected” for ineligible students.

• student_city: This column displays the student’s city from where he/she belongs.

Query:

SELECT student_city,
CASE WHEN TRY_CONVERT(DATE,student_dob) > '04-07-1992 00:00:00'
THEN student_name
WHEN TRY_CONVERT(DATE,student_dob) <= '04-07-1992 00:00:00'
THEN 'Ineligible'
ELSE
'Inappropriate'
END AS voter_name
FROM students;

Output:

The voter_name column along with the student’s city that are eligible for voting is displayed.

Result

Explanation: As we see from the output, it consists of the selected_students column list that contains the corresponding names of all the eligible students to vote, along with their city names.

As seen from the above query:

We use the CASE, WHEN, and THEN clauses in our query. We write 2 cases:

• 1st case: Condition for eligibility to vote, which is > ’04-07-1992′.
• 2nd case: Condition for ineligibility to vote, which is <= ’04-07-1992′.

We need to check these conditions with our student_dob column data. However, to do the following, we first need to convert the student_dob column into the appropriate data type for comparison.

So to convert the student_dob column into the date data type we use the TRY_CONVERT() function providing the student_dob column as the input.

We also provide a third output as ”Inappropriate” as a default output to be displayed in case none of the 2 conditions are satisfied using the ELSE clause as seen in the query.

Difference Between TRY_CONVERT vs CONVERT Function

SQL Server function	TRY_CONVERT	CONVERT
Query	SELECT TRY_CONVERT(DATE, student_dob) FROM students;	SELECT CONVERT(INT,student_dob) FROM students;
Conversion Process	Incompatible data types result in NULL values as output	Incompatible data types result in Failed Conversions
Output	No such error message is displayed for failed conversions	The Error message is displayed stating failed to convert the given data types
Data State	When using the TRY_CONVERT() function, input data stored need not be of compatible format	When using the CONVERT() function, input data must be of compatible format, or else it will result in failed conversions.
Use Case	Not suitable to be used when arithmetic operations are to be performed over the data stored in the database, due to substitution of NULL values in case of failed data conversions	Best suited to be used when certain arithmetic operations are required to be performed over the data to protect our data from a dirty state, due to the absence of NULL values for failed conversions

Conclusion

From our example, we have seen how we could utilize the SQL server’s TRY_CONVERT() function along with a combination of certain other functions to fulfill a certain requirement. Then we accordingly compare them with one another to filter out those specific records from the database tables, in the form of results displayed as the output.