Converts a given value to a different, but compatible, type.

Syntax

---

CAST(<Expression> AS <DataType>
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>]
)
CAST(<Expression>, <DataType>
[DATE FORMAT <DateFormat>]
[TIME FORMAT <TimeFormat> [AM LITERAL <AMLiteral> PM LITERAL <PMLiteral>]]
[DECIMAL CHAR <DecimalChar>]
[BOOLEAN TRUE LITERAL <TrueLiteral> FALSE LITERAL <FalseLiteral>])

<Expression> =

Type of:

CHARACTER|CHAR
CHARACTER VARYING|VARCHAR
GUID
BYTE
BYTE VARYING|VARBYTE
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT
DECIMAL|NUMERIC
DATE
TIME
TIMESTAMP
INTERVAL YEAR
INTERVAL YEAR TO MONTH
INTERVAL MONTH
INTERVAL DAY
INTERVAL DAY TO HOUR
INTERVAL DAY TO MINUTE
INTERVAL DAY TO SECOND
INTERVAL DAY TO MSECOND
INTERVAL HOUR
INTERVAL HOUR TO MINUTE
INTERVAL HOUR TO SECOND
INTERVAL HOUR TO MSECOND
INTERVAL MINUTE
INTERVAL MINUTE TO SECOND
INTERVAL MINUTE TO MSECOND
INTERVAL SECOND
INTERVAL SECOND TO MSECOND
INTERVAL MSECOND

<DataType> =

CHARACTER|CHAR [(<Length>]) [<CollationName>]
CHARACTER VARYING|VARCHAR [(<Length>]) [<CollationName>]
GUID
BYTE [(<LengthInBytes>])
BYTE VARYING|VARBYTE [(<LengthInBytes>])
BINARY LARGE OBJECT|BLOB
CHARACTER LARGE OBJECT|CLOB [<CollationName>]
BOOLEAN|BOOL
SMALLINT
INTEGER|INT
BIGINT
FLOAT [(<Precision>,<Scale>)]
DECIMAL|NUMERIC [(<Precision>,<Scale>)]
DATE
TIME
TIMESTAMP
INTERVAL YEAR [TO MONTH]
INTERVAL MONTH
INTERVAL DAY [TO HOUR|MINUTE|SECOND|MSECOND]
INTERVAL HOUR [TO MINUTE|SECOND|MSECOND]
INTERVAL MINUTE [TO SECOND|MSECOND]
INTERVAL SECOND [TO MSECOND]
INTERVAL MSECOND

<DateFormat> =

YYYY or YY = Year digits
MM or M = Month digits
DD or D = Day digits
Any other character = literal

<TimeFormat> =

HH or H = Hours digits
MM or M = Minutes digits
SS or S = Seconds digits
ZZZ or Z = Milliseconds digits
N = AM/PM literal
Any other character = literal

Returns

---

Input converted to specified data type, provided
that there aren't any numeric overflow errors

Usage

---

The CAST function converts a given value to a different, but compatible, type. The following table details the various types and which types they can be converted to:


The CAST function can also be used to convert a string value from one collation to the other without changing the actual base string type.

When casting a value to and from a string type such as CHAR or VARCHAR, you can use the formatting extensions to specify how the resultant string or value will look once the conversion takes place. The following tables shows which source types can use which formatting extensions:


Examples

---

SELECT 'Date/Time: '+CAST(TransDateTime AS VARCHAR(25)) +
'User: '+TransUser AS TransInfo
FROM Transactions

SELECT CAST(LastInvoiceDate AS VARCHAR(24)
DATE FORMAT 'm/dd/yyyy' TIME FORMAT 'h:mm:ss n')
FROM customer

SQL 2003 Standard Deviations

---

This function deviates from the SQL 2003 standard in the following ways: