SQL Server Conversion Functions
Conversion Functions in SQL Server are used to convert a value from one data type to another data type.
What are Conversion Functions?
SQL Server stores different types of data such as integers, strings, decimal values and dates. Sometimes we need to convert one data type into another data type.
SQL Server provides conversion functions such as CAST(), CONVERT() and TRY_CAST() to perform these conversions.
Suppose the value '100' is stored as text. If we want to perform mathematical operations on it, we can convert it into an integer.
Common Conversion Functions
| Function | Purpose | Example |
|---|---|---|
| CAST() | Converts an expression from one data type to another. |
CAST('100' AS INT)
|
| CONVERT() | Converts an expression into another data type and supports style formatting. |
CONVERT(INT, '100')
|
| TRY_CAST() | Attempts to convert a value and returns NULL if conversion fails. |
TRY_CAST('ABC' AS INT)
|
| TRY_CONVERT() | Attempts conversion and returns NULL when conversion is unsuccessful. |
TRY_CONVERT(INT, 'ABC')
|
1. CAST() Function
The CAST() function is used to convert an expression from one data type to another data type.
Syntax
CAST(expression AS data_type)
Example 1: VARCHAR to INT
SELECT CAST('100' AS INT);
Example 2: INT to VARCHAR
SELECT CAST(500 AS VARCHAR(10));
Example 3: DECIMAL to INT
SELECT CAST(125.75 AS INT);
2. CONVERT() Function
The CONVERT() function is also used to convert one data type into another.
One important feature of CONVERT() is that it can use a style parameter for formatting date and time values.
Syntax
CONVERT(data_type, expression)
Example
SELECT CONVERT(INT, '250');
Date Conversion Example
SELECT CONVERT(VARCHAR(10), GETDATE(), 103);
Style 103 formats the date in dd/mm/yyyy format.
3. TRY_CAST() Function
The TRY_CAST() function attempts to convert a value into the specified data type.
If the conversion is successful, the converted value is returned. If the conversion fails, SQL Server returns NULL instead of raising a conversion error.
Syntax
TRY_CAST(expression AS data_type)
Successful Conversion
SELECT TRY_CAST('500' AS INT);
Failed Conversion
SELECT TRY_CAST('ABC' AS INT);
4. TRY_CONVERT() Function
The TRY_CONVERT() function attempts to convert an expression into the specified data type.
If conversion cannot be performed, it returns NULL.
Syntax
TRY_CONVERT(data_type, expression)
Example
SELECT TRY_CONVERT(INT, 'ABC');
CAST() vs CONVERT()
| CAST() | CONVERT() |
|---|---|
| Standard SQL conversion function. | SQL Server-specific conversion function. |
| Syntax is simple and readable. | Supports style formatting. |
CAST(value AS INT)
|
CONVERT(INT, value)
|
Practical Example with Student Table
Suppose we have a Students table where Marks are stored as VARCHAR values.
CREATE TABLE Students
(
StudentId INT,
StudentName VARCHAR(100),
Marks VARCHAR(10)
);
Insert Sample Data
INSERT INTO Students
VALUES
(1, 'Rahul', '85'),
(2, 'Priya', '92'),
(3, 'Amit', '76');
Convert Marks into INT
SELECT
StudentName,
Marks,
CAST(Marks AS INT) AS NumericMarks
FROM Students;
Calculate Total Marks
SELECT
SUM(CAST(Marks AS INT)) AS TotalMarks
FROM Students;
Calculate Average Marks
SELECT
AVG(CAST(Marks AS INT)) AS AverageMarks
FROM Students;
Practical Date Conversion
We can convert a date into a formatted string using CONVERT().
SELECT CONVERT(VARCHAR(10), GETDATE(), 103)
AS FormattedDate;
05/10/2026
When Do We Use Conversion Functions?
- When numeric data is stored as text.
- When text needs to be converted into numeric values.
- When dates need to be displayed in a particular format.
- When data from different sources has different data types.
- When performing calculations on converted values.
- When invalid conversion should return NULL instead of causing an error.
Important Points
- CAST() converts an expression to another data type.
- CONVERT() also converts data types and supports style formatting.
- TRY_CAST() returns NULL when conversion fails.
- TRY_CONVERT() returns NULL when conversion fails.
- Conversion is useful when different data types need to work together.
- Always make sure that the source value can be safely converted to the target data type.
Summary
SQL Server Conversion Functions are used to convert values from one data type to another. In this topic, we learned CAST(), CONVERT(), TRY_CAST() and TRY_CONVERT(). These functions are very useful when working with numeric values, text values, dates and data received from different sources.