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

Guide

Reference