How to Use the CAST() Function in SQL Server: A Comprehensive Guide
☰Fullscreen
The CAST() function converts a value (of any type) into a specified datatype.
Syntax:
<span class="pln"> CAST</span><span class="pun">(</span><span class="pln">expression AS datatype</span><span class="pun">(</span><span class="pln">length</span><span class="pun">))</span><span class="pln"> </span>
| Value | Description |
|---|---|
| expression | Required. The value to convert |
| datatype | Required. The datatype to convert expression to. Can be one of the following: bigint, int, smallint, tinyint, bit, decimal, numeric, money, smallmoney, float, real, datetime, smalldatetime, char, varchar, text, nchar, nvarchar, ntext, binary, varbinary, or image |
| (length) | Optional. The length of the resulting data type (for char, varchar, nchar, nvarchar, binary and varbinary) |
Works in: SQL Server (starting with 2008), Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
Code:
<span class="pln"> SELECT CAST</span><span class="pun">(</span><span class="lit">25.65</span><span class="pln"> AS varchar</span><span class="pun">)</span><span class="pln"> SELECT CAST</span><span class="pun">(</span><span class="str">'2017-08-25'</span><span class="pln"> AS datetime</span><span class="pun">)</span><span class="pln"> </span>
Output:
The above code will produce the following result-
<span class="pln"> </span><span class="lit">25.65</span><span class="pln"> </span><span class="lit">2017</span><span class="pun">-</span><span class="lit">08</span><span class="pun">-</span><span class="lit">25</span><span class="pln"> </span><span class="lit">00</span><span class="pun">:</span><span class="lit">00</span><span class="pun">:</span><span class="lit">00.000</span><span class="pln"> </span>
Consider the Employees Table below
The following queries convert, DateOfBirth's DateTime datatype to NVARCHAR.
Code:
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> DOB</span><span class="pun">,</span><span class="pln"> CAST</span><span class="pun">(</span><span class="pln">DOB </span><span class="kwd">as</span><span class="pln"> nvarchar</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">ConvertedDOB</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> </span>
In this query, we are using CAST() function, to convert Id (int) to nvarchar, so it can be appended with the NAME column. If you remove the CAST() function, you will get an error stating - 'Conversion failed when converting the nvarchar value 'Sam - ' to data type int.'
Code:
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="typ">Id</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> </span><span class="str">' - '</span><span class="pln"> </span><span class="pun">+</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">Id</span><span class="pln"> AS NVARCHAR</span><span class="pun">)</span><span class="pln"> AS </span><span class="pun">[</span><span class="typ">Name</span><span class="pun">-</span><span class="typ">Id</span><span class="pun">]</span><span class="pln"> FROM </span><span class="typ">Employee</span><span class="pln"> </span>
Now let's look at a practical example of using CAST function. Consider the registrations table below.
Query:
<span class="pln"> </span><span class="typ">Select</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">RegisteredDate</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> DATE</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">RegistrationDate</span><span class="pun">,</span><span class="pln"> COUNT</span><span class="pun">(</span><span class="typ">Id</span><span class="pun">)</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> </span><span class="typ">TotalRegistrations</span><span class="pln"> </span><span class="typ">From</span><span class="pln"> tblRegistrations </span><span class="typ">Group</span><span class="pln"> </span><span class="typ">By</span><span class="pln"> CAST</span><span class="pun">(</span><span class="typ">RegisteredDate</span><span class="pln"> </span><span class="kwd">as</span><span class="pln"> DATE</span><span class="pun">)</span><span class="pln"> </span>
The following are the differences between the 2 functions.
- Cast is based on ANSI standard and Convert is specific to SQL Server. So, if portability is a concern and if you want to use the script with other database applications, use Cast().
- Convert provides more flexibility than Cast. For example, it's possible to control how you want DateTime datatypes to be converted using styles with convert function.
The general guideline is to use CAST(), unless you want to take advantage of the style functionality in CONVERT().
Prerequisite Code:
<span class="pln"> </span><span class="typ">Drop</span><span class="pln"> table </span><span class="typ">Employee</span><span class="pln"> </span><span class="typ">Create</span><span class="pln"> </span><span class="typ">Table</span><span class="pln"> </span><span class="typ">Employee</span><span class="pun">(</span><span class="pln"> </span><span class="typ">Id</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL primary key</span><span class="pun">,</span><span class="pln"> </span><span class="typ">Name</span><span class="pln"> nvarchar</span><span class="pun">(</span><span class="lit">50</span><span class="pun">),</span><span class="pln"> DOB datetime </span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'1'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Rambo'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1986-11-14 08:26:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'2'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Azam'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1984-10-10 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'3'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Inza'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1996-07-26 08:26:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'4'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Jaman'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-11-02 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'5'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'Asad'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1991-01-03 03:32:00.000'</span><span class="pun">)</span><span class="pln"> insert </span><span class="kwd">into</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> values </span><span class="pun">(</span><span class="str">'6'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'kamran'</span><span class="pun">,</span><span class="pln"> </span><span class="str">'1990-11-02 03:32:00.000'</span><span class="pun">)</span><span class="pln"> </span><span class="typ">Select</span><span class="pln"> </span><span class="pun">*</span><span class="pln"> </span><span class="kwd">from</span><span class="pln"> </span><span class="typ">Employee</span><span class="pln"> </span>
No Questions Data Available.
No Program Data.