SQL CAST Function

The CAST function converts values from one data type to another.

If the conversion fails, an error is returned.

CAST supports limited formatting of the return values.

Example

#

This example converts different data types to other data types.

SELECT CAST(48.3 AS CHAR(4)) AS Char,
       CAST('4' AS DECIMAL(10,2)) AS Decimal,
       CAST('2021-09-10' AS DATETIME2) AS DateTime
Result:  1 record
Char Decimal DateTime
4.00 48.3 2021-09-10 00:00:00.0000000

Using CAST

#

CAST has a limited syntax with little flexibility.

The newer FORMAT function can be an alternative in certain situations.

FORMAT offers greater flexibility in formatting specific and local-aware values.

However, CAST is more performant than FORMAT.

Syntax

Syntax for the CAST function.

CAST(value AS data_type [,(length) ])

value -- any valid value, usually a column name.

data_type -- the target data type.

length -- optional. Length of the target data type.


More Examples

CAST. Decimal places.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products and their prices with one decimal place.
SELECT ProductName, 
       CAST(UnitPrice AS DEC(5,1)) AS Price
  FROM Product
Result:  78 records
ProductName Price
Chai 18.0
Chang 19.0
Aniseed Syrup 10.0
Chef Anton's Cajun Seasoning 22.0
Chef Anton's Gumbo Mix 21.4
Note: The returned values are rounded to the nearest integer.

CAST. Formatting.

PRODUCT
Id
ProductName
SupplierId
UnitPrice
Package
IsDiscontinued
Problem: List products with prices in $ amounts.
 SELECT ProductName, 
       '$' + CAST(UnitPrice AS VARCHAR) AS '$ Price'
  FROM Product
Result:  78 records
ProductName $ Price
Chai $18.00
Chang $19.00
Aniseed Syrup $10.00
Chef Anton's Cajun Seasoning $22.00
Chef Anton's Gumbo Mix $21.35

CAST versus CONVERT

#

Both functions are commonly used in data conversions, but there are differences.

CAST is an ANSI Standard and is supported on SQL Server, MySQL, ORACLE, and others.

CONVERT is Microsoft specific and works only on SQL Server

CAST limits formatting to enable data conversions.

CONVERT on the other hand, can be used for formatting date/time, and money data types.

CAST has a simple syntax.

CONVERT offers tranlation and formatting flexibility with the style parameter.


You may also like



Guides


vsn 3.1