CAST and CONVERT for type changes in SQL

Introduction

We use CAST and CONVERT to change data from one type to another, like turning text into numbers or dates. This helps us work with data in the right way.

When you have a number stored as text and want to do math with it.

When you want to show a date in a different format.

When combining data from different tables with different data types.

When you need to compare values but their types don't match.

When importing data that is not in the correct type and needs conversion.

Syntax

SQL

CAST(expression AS target_data_type)
CONVERT(target_data_type, expression [, style])

CAST is standard SQL and works in most databases.

CONVERT is often used in SQL Server and allows an optional style for formatting.

Examples

This changes the text '123' into the number 123.

SQL

SELECT CAST('123' AS INT);

This converts the current date to a string in 'YYYY-MM-DD' format.

SQL

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

This converts the decimal number 123.45 to the integer 123 by dropping the decimal part.

SQL

SELECT CAST(123.45 AS INT);

Sample Program

This query converts a text date to a date type, then converts it back to a string in MM/DD/YYYY format.

SQL

SELECT CAST('2024-06-01' AS DATE) AS ConvertedDate,
       CONVERT(VARCHAR, CAST('2024-06-01' AS DATE), 101) AS FormattedDate;

Important Notes

CAST is more portable across different SQL databases.

CONVERT's style parameter is useful for date and time formatting in SQL Server.

Be careful converting types that don't match well, like text that isn't a number.

Summary

CAST and CONVERT change data types to help with calculations and formatting.

CAST is standard SQL; CONVERT has extra formatting options in some databases.

Use them to fix data type mismatches and display data nicely.