How to convert data types in SQL
In relational databases, data types define how the data is stored, processed, and retrieved. But real-world data is often messy or inconsistent, coming from various sources like APIs, flat files, or user input forms. In such scenarios, type conversion becomes an essential part of SQL development.
Two core SQL functions—CAST() and CONVERT()—allow you to explicitly change one data type into another. This article provides an in-depth look at both functions, their syntax, capabilities, and real-world use cases. We’ll also discuss best practices and help you decide which function to use depending on your scenario.
Learn SQL online
Introduction to CAST and CONVERT
SQL provides two explicit conversion functions cast and convert:
CAST
The CAST() function is part of the ANSI SQL standard, which means it is portable across different RDBMS like SQL Server, PostgreSQL, MySQL, and Oracle. If you need code that will work on multiple platforms, CAST() is the safer option.
It has the following syntax:
CAST(expression AS target_data_type)
CONVERT
CONVERT() is a proprietary function in Transact-SQL (T-SQL), used primarily in Microsoft SQL Server. While it supports basic conversion like CAST(), it offers extra flexibility, especially when converting date/time types with formatting styles.
Syntax (SQL Server):
CONVERT(target_data_type, expression [, style])
Use Cases and Examples
Let’s explore some practical use cases where CAST() and CONVERT() are commonly used. Often, data imported from external sources like CSV files or spreadsheets is stored as strings. You’ll need to convert those strings into numeric types for calculation
CAST examples
SELECT CAST(89.67 AS INT) AS RoundedValue;
SELECT CAST(GETDATE() AS VARCHAR(30)) AS DateAsText;
CONVERT examples
SELECT CONVERT(INT, 89.67) AS RoundedValue;
SELECT CONVERT(INT, '500') + 100 AS Total;
Converting and Formatting Dates
Handling and formatting date and time values is one of the areas where CONVERT() shines—especially in SQL Server.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS FormattedDate;
Here, the style code 101 corresponds to the U.S. date format (mm/dd/yyyy). SLQ server provides for a number of formats
| Style | Format | Description |
|---|---|---|
| 101 | mm/dd/yyyy | U.S. standard |
| 103 | dd/mm/yyyy | British/French |
| 112 | yyyymmdd | ISO format |
| 120 | yyyy-mm-dd hh:mi:ss | ISO 8601 with time |
Type Compatibility in UNION Queries
When combining datasets using UNION or UNION ALL, SQL requires corresponding columns to have the same data types. Explicit casting ensures compatibility.
SELECT CAST(Amount AS VARCHAR(10)) AS UnifiedAmount FROM Sales
UNION ALL
SELECT RefundAmount FROM Returns;
Best Practices
Default to
CAST()for standard, portable SQL logic.Use
CONVERT()when working in SQL Server and you need formatted output (especially with dates).Always validate or clean string data before casting to numeric types to avoid runtime errors.
Use
TRY_CAST()orTRY_CONVERT()in SQL Server if there’s a risk of conversion failure—these returnNULLinstead of throwing an error.
Summery
Both CAST() and CONVERT() allow SQL developers to convert data from one type to another, a crucial operation in data transformation, reporting, and cleaning.
Use
CAST()when you want portability, clarity, and standards-compliant code.Use
CONVERT()when working with SQL Server and you need precise control over formatting, especially forDATETIMEtypes.
Please see the other SQL video learning articles below as part of this learning SQL online course video.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
