CAST (TSQL Function)
Converts the argument to the specified datatype.
Syntax
CAST ( expression AS datatype )
Arguments
- expression
- is an expression of any type.
- datatype
- is the datatype the expression will be converted to.
- The default precision for
varbinary
,varchar
andchar
is 30 if not specified. - The default precision for
numeric
is 18 if not specified.
- The default precision for
Return Types
Returns a value of type datatype.
Remarks
If expression is NULL, NULL is returned.
Usually, if the result doesn’t fit in target datatype, an error is raised.
But in some cases, the result is truncated or rounded as specified in the Remarks column.
From | To | Remarks |
---|---|---|
varbinary | varbinary | Truncates if result is larger than target precision. |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | ||
varchar, char | varchar, char | Truncates if result is larger than target precision. |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | ||
money | Rounds | |
numeric | Rounds. Implicit cast is not allowed. | |
float | ||
date | same as CONVERT(date, expression, 0) | |
time | same as CONVERT(time, expression, 0) | |
datetime | same as CONVERT(datetime, expression, 0) | |
bit | varchar, char | |
bit | ||
tinyint, smallint, int, bigint | ||
money | ||
numeric | ||
float | ||
tinyint, smallint, int, bigint | varchar, char | |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | ||
money | ||
numeric | ||
float | ||
datetime | same as DATEADD(dd, expression, '1900-01-01') | |
money | varchar, char | Two digits after the decimal point are printed. |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | Rounds | |
money | ||
numeric | Rounds | |
float | ||
numeric | varchar, char | |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | Truncates | |
money | Rounds | |
numeric | Rounds | |
float | ||
float | varchar, char | |
bit | if expression == 0, returns 0. Else, 1. | |
tinyint, smallint, int, bigint | Truncates | |
money | Rounds | |
numeric | Rounds | |
float | ||
date | varchar, char | same as CONVERT(varchar(n), expression, 121). |
date | ||
datetime | ||
time | varchar, char | same as CONVERT(varchar(n), expression, 121) |
time | ||
datetime | The day will be 1900-01-01. | |
datetime | varchar, char | same as CONVERT(varchar(n), expression, 121) |
date | The time part is discarded. | |
time | The date part is discarded. | |
datetime |
MS SQL Server allows implicit conversion from
varchar
tonumeric
. RSQL doesn’t allow that, and you should always make an explicit cast, specifying the proper precision and scale.
print 12.34 + '10'
is not allowed by RSQL.
If datatype is varbinary
, varchar
or char
and precision is omitted, it defaults to 30.
If datatype is numeric
and precision is omitted, it defaults to 18.
A CAST
function from varchar/char
to date/time/datetime
is always transformed to CONVERT(..., 0)
function.
The result depends on the current LANGUAGE
setting.
A CAST
function from date/time/datetime
to varchar/char
is always transformed to CONVERT(..., 121)
function.
The result depends on the current LANGUAGE
setting.
MS SQL Server allows to cast an
int
tovarchar
orchar
with insufficient precision, returning the string'*'
.
RSQL doesn’t allow that and raises an error.
print cast(1234 as varchar(3))
raises an error with RSQL.
For more control on the conversion, see the function CONVERT
or FORMAT
.
Examples
PRINT 'I ordered ' + CAST(3 AS VARCHAR(100)) + ' books';
PRINT CAST('Hello World' AS VARCHAR(5)); -- result will be truncated
DECLARE @d DATETIME = '1955-06-12';
PRINT CAST(@d as VARCHAR(100));
PRINT CAST('2016-03-20' AS DATETIME);
-- default precision for VARCHAR is 30. Result will be truncated.
PRINT CAST('Then two white pigeons came in by the kitchen window.' AS VARCHAR)
PRINT CAST( 12.3456 AS VARCHAR(100));
PRINT CAST($12.3456 AS VARCHAR(100)); -- two decimals are printed
The result is:
I ordered 3 books
Hello
1955-06-12 00:00:00.000
2016-03-20 00:00:00
Then two white pigeons came in
12.3456
12.35