Cast and Convert Function

Sometimes we need to change our data type, example: from Integer type, we want to change into String type. In VB, we can user Cstr, Cint, Clng, etc. but how if we want do that in SQL Query?

In SQL Server, they already provide it for us. We can use Convert or Cast Function. These two functions have a same usability. The different on these two functions only their syntax.

Here the Cast Function Syntax:

CAST ( expression AS data_type ) *

and the Convert Function Syntax:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )*

Now, I will show a little experiments how to use those functions.

We will use the legendary database: Northwind.

Then, we use the Employees Table on Northwind.

Ok. if you open the object browsers with F8 in SQL Query Analyzer, and in Employees Table you right click and choose “Script Object to New Windows As” -> “Create”, we will get the Create Query automatic generated from SQL Query Analyzer.

Here the code:

CREATE TABLE [Employees] (

[EmployeeID] [int] IDENTITY (1, 1) NOT NULL ,

[LastName] [nvarchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[FirstName] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,

[Title] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[TitleOfCourtesy] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[BirthDate] [datetime] NULL ,

[HireDate] [datetime] NULL ,

[Address] [nvarchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[City] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Region] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[PostalCode] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Country] [nvarchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[HomePhone] [nvarchar] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Extension] [nvarchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[Photo] [image] NULL ,

[Notes] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

[ReportsTo] [int] NULL ,

[PhotoPath] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED

(

[EmployeeID]

) ON [PRIMARY] ,

CONSTRAINT [FK_Employees_Employees] FOREIGN KEY

(

[ReportsTo]

) REFERENCES [Employees] (

[EmployeeID]

),

CONSTRAINT [CK_Birthdate] CHECK ([BirthDate] <>

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

you can see the EmployeeID field is Integer. Okay, we will change it with Cast and Convert Function.

The Cast Function:

Select Cast(EmployeeID As Nvarchar(10)), BirthDate from Employees

the result will be:

you see the result, the first column will be set into (No Column Name), it's mean employeeId has been changed and not given name yet.

Let's prove EmployeeID has Change. In the beginning, the EmployeeID was Integer Type, that means, if we use statistic function on it, it will work. But, when we change it into the nvarchar type, we will get error from our query.

Testing Query:

Select Sum(EmployeeID) As Summary from Employees

The Result:

Summary

------------

45

compare with this query:

Select Sum(Cast(EmployeeID As Nvarchar(10))) As Summary from Employees

The result:

Server: Msg 409, Level 16, State 2, Line 1

The sum or average aggregate operation cannot take a nvarchar data type as an argument.

Ok. The same conditions is working for Convert Function.

Here the Convert Function Example:

Select Convert(Nvarchar(10), EmployeeID), BirthDate from Employees

The result is same with the Cast Function Result. You can see the image.

Ok Folks. That's all for today.

Enjoy it...