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.
