What is Difference between CAST vs CONVERT in SQL Server?

In Microsoft SQL Server, both CAST and CONVERT are used to change a value from one data type to another, but they differ in syntax, flexibility, and portability.

1. Basic Syntax

CAST

CAST(expression AS data_type)

Example:

SELECT CAST(123.456 AS INT);

Result:

123

CONVERT

CONVERT(data_type, expression [, style])

Example:

SELECT CONVERT(INT, 123.456);

Result:

123

2. Main Differences

3. Date Formatting (Biggest Practical Difference)

CONVERT supports a style code, especially useful for formatting dates.

Example:

SELECT CONVERT(VARCHAR, GETDATE(), 103);

Result:

12/05/2026

Here:

103 = British/French date format (dd/mm/yyyy)

Equivalent with CAST:

SELECT CAST(GETDATE() AS VARCHAR);

This gives a default format only — no custom style control.

4. Common Style Codes in CONVERT

Example:

SELECT CONVERT(VARCHAR, GETDATE(), 120);

5. Performance Difference

In most real-world cases:

• Performance difference is negligible.
• SQL Server optimizes both similarly.

Choose based on:

• readability
• portability
• formatting needs

6. When to Use Which?

Use CAST when:

• Writing portable SQL
• Following ANSI standards
• Simple type conversion is enough

Example:

SELECT CAST(price AS DECIMAL(10,2))
FROM Products;

Use CONVERT when:

• Working specifically in SQL Server
• You need formatted date/time output
• You need style codes

Example:

SELECT CONVERT(VARCHAR(10), OrderDate, 103)
FROM Orders;

7. TRY_CAST and TRY_CONVERT

SQL Server also provides safer versions:

TRY_CAST('abc' AS INT)

Returns:

NULL

instead of throwing an error.

Similarly:

TRY_CONVERT(INT, 'abc')

Very useful for data cleaning and ETL work.

Quick Summary

A common rule developers follow:

Use CAST by default, and use CONVERT when you need formatting styles.