SQL Commands


MSSQL CAST Tutorial

The MSSQL CAST FUNCTION is used to convert an expression from one data type to another datatype

MSSQL CAST FUNCTION Syntax

CAST (expression AS data_type [(length)])

Data_type is the target data type. Length is an optional integer which specifies length of target data type. If length is not mentioned, it is taken 30 by default. With the cast function you have to specify length of data type if your source length is greater than 30.

MSSQL ROW_NUMBER FUNCTION

Note: While converting data types that differ in decimal places, the output value is either rounded off or truncated as per below table.

Source Data Type Targeted Data Type Behaviour
Numeric Numeric Round
Numeric Int Truncate
Money Numeric Round
Money Int Round
Float Int Truncate
Float Numeric Round
Float Datetime Round ref*
Datetime Int Round
Non Numeric(char, nchar, varchar
or nvarchar)
Int, float, numeric,
or decimal
Error Message
Empty String numeric, or decimal Error Message

Note: For Source Data Type/Float-datetime: Conversion of float values that use scientific notation to decimal or numeric is restricted to values of precision 17 digits only. Any value with precision higher than 17 rounds to zero.

MSSQL CAST FUNCTION Example

Student ID Student ID Score
1001 Henry 98.09
1002 Tina 38.63
1003 John 49.56
1004 Mile 89.01
1005 Tara 73.02
1006 Sophie 29.82

Where Student ID is int type, Student name is Char type and score is float type.

Below is an example on cast function without mentioning length of targeted data type:

SELECT StudentID, StudentName, CAST (Score AS int)
FROM Student;

MSSQL CAST FUNCTION Results

MSSQL ROW_NUMBER function output will give result below.

Student ID Student ID Score
1001 Henry 98
1002 Tina 38
1003 John 49
1004 Mile 89
1005 Tara 73
1006 Sophie 29

MSSQL CAST FUNCTION Example 2

Below is an example on cast function with specified length of 4 characters as the targeted data type

SELECT StudentID, StudentName, CAST (Score AS char(4))
FROM Student;

MSSQL CAST FUNCTION Results

MSSQL CAST function output with specified length of targeted data type will now provide the following output results.

Student ID Student ID Score
1001 Henry 98.0
1002 Tina 38.6
1003 John 49.5
1004 Mile 89.0
1005 Tara 73.0
1006 Sophie 29.8

MSSQL CAST FUNCTION Summary

Cast is useful when a match (or condition) is needed between data type of two different types. Cast is required to preserve decimal values while conversion between numeric and decimal values.

 

BookMark This Page




EnglishFrenchGermanItalianPortugueseRussianSpanish