The CAST and convert functions provide similar functionality. They are used to convert a value
from one data type to another. So let's take a look at a practical example. The
example is developed in SQL Server 2008 using the SQL Server Management Studio.
CAST () Function: The Cast() function is used to convert a data
type variable or data from one data type to another data type. The Cast()
function provides a data type to a dynamic parameter (?) or a NULL value.
Syntax:
CAST
( [Expression] AS Datatype)
The data type to which you are casting an
expression is the target type. The data type of the expression from which you
are casting is the source type.
Example
:
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
set @A=50
set @B=15
set @C=35
Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result
Output:
100
Example
:
DECLARE @Z char(30)
SELECT @Z=current_timestamp
select CAST (@Z as date) as result
Output:
2015-03-30
convert () Function: When you convert expressions from one type to
another, in many cases there will be a need within a stored procedure or other
routine to convert data from a datetime type to a varchar type. The Convert
function is used for such things. The CONVERT() function can be used to display
date/time data in various formats.
Syntax
:
CONVERT(data_type(length), expression, style)
Style - style values for
datetime or smalldatetime conversion to character data. Add 100 to a style value to get a four-place
year that includes the century (yyyy).
Example:
In
this example we take a style value 108 which defines the following format:
hh:mm:ss
Now use the above style in the following query:
select convert(varchar(20),GETDATE(),108)
Output:
14:59:55
Example
:
In this example we use the style value 107 which
defines the following format:
Mon
dd, yy
Now use that style in the following query:
select convert(varchar(20),GETDATE(),107)
Output:
Mar 30, 2015
Example:
In this example we see different style values
which defines the following format.
SELECT CONVERT(VARCHAR(15),GETDATE(),6)
go
SELECT CONVERT(VARCHAR(16),GETDATE(),106)
go
SELECT CONVERT(VARCHAR(24),GETDATE(),113)
Output:
30 Mar 15
30 Mar 2015
30
Mar 2015 15:02:51:150