---

The CAST() function converts a value (of any type) into a specified datatype.

Syntax:

CAST(expression AS datatype(length))

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:

SELECT CAST(25.65 AS varchar)
SELECT CAST('2017-08-25' AS datetime)

Output:

The above code will produce the following result-

25.65
2017-08-25 00:00:00.000

Consider the Employees Table below

The following queries convert, DateOfBirth's DateTime datatype to NVARCHAR.

Code:

Select Id, Name, DOB, CAST(DOB as nvarchar) as ConvertedDOB
from Employee 

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:

Select Id, Name, Name + ' - ' + CAST(Id AS NVARCHAR) AS [Name-Id]
FROM Employee 

Now let's look at a practical example of using CAST function. Consider the registrations table below.

Query:

Select CAST(RegisteredDate as DATE) as RegistrationDate,
COUNT(Id) as TotalRegistrations
From tblRegistrations
Group By CAST(RegisteredDate as DATE)

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:

Drop table Employee

Create Table Employee(
 Id int NOT NULL primary key,
 Name nvarchar(50),
 DOB datetime
)

insert into Employee values ('1', 'Rambo', '1986-11-14 08:26:00.000')
insert into Employee values ('2', 'Azam', '1984-10-10 03:32:00.000')
insert into Employee values ('3', 'Inza', '1996-07-26 08:26:00.000')
insert into Employee values ('4', 'Jaman', '1990-11-02 03:32:00.000')
insert into Employee values ('5', 'Asad', '1991-01-03 03:32:00.000')
insert into Employee values ('6', 'kamran', '1990-11-02 03:32:00.000')

Select * from Employee

---