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 and char is 30 if not specified.
  • The default precision for numeric is 18 if not specified.

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 to numeric. 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 to varchar or char 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
RSQL, a simple alternative to Microsoft SQL Server