In this article, we will see how to use the cast and convert functions in SQL Server 2012. 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 2012 using the SQL Server Management Studio.
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.
CAST ( [Expression]
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.
DECLARE @A varchar(2)
DECLARE @B varchar(2)
DECLARE @C varchar(2)
Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result
DECLARE @Z char(30)
select CAST (@Z as date) as result
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.
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).
In this example we take a style value 108 which defines the following format:
Now use the above style in the following query:
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:
In this example we see different style values which defines the following format.