CAST & CONVERT Function | Database

CAST

CAST function in SQL converts data from one data type to another. Syntax of the CAST function is as follows:

CAST (expression AS [data_type])

data_type is a valid data type in the database you are working with.

In below example, SQL implicitly converts the character string ‘1’ to the number 1. When you use two values with different data types, SQL will try to convert the lower data type to the higher one before it can process the calculation. This is known as an Implicit Conversion. To do explicit conversions use CAST() function to explicitly convert a value of one type to another.

SELECT 1 + '1'
SELECT 1 + CAST('1' AS int)

/* Result */
2
2

Conversion using CAST() will be truncated or a rounded value based on data type.

SELECT CAST(5.95 AS DEC(3,0)) result
SELECT CAST('3019-03-14' AS DATETIME) result

/* Result */
6
3019-03-14 00:00:00

CONVERT

CONVERT() function allows you to convert a value of one type to another. The syntax of the CONVERT function is as follows:

CONVERT (expression, [data_type])

The CONVERT() is similar to the CAST() function. Following is example of explicit conversion using CONVERT()

SELECT CONVERT(9.95, int) result
SELECT CONVERT('3019-03-14', DATETIME) result
SELECT CONVERT('Hello World', VARCHAR(13)) result

/* Result */
10
3019-03-14 00:00:00
Hello World

Reference

CONVERT() Function