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.
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
| Char | Decimal | DateTime |
|---|---|---|
| 4.00 | 48.3 | 2021-09-10 00:00:00.0000000 |
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 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.
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName,
CAST(UnitPrice AS DEC(5,1)) AS Price
FROM Product
| 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 |
 |
|
| PRODUCT |
|---|
| Id |
| ProductName |
| SupplierId |
| UnitPrice |
| Package |
| IsDiscontinued |
SELECT ProductName,
'$' + CAST(UnitPrice AS VARCHAR) AS '$ Price'
FROM Product
| 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 |
|
|
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.
