Other function in SQL Server

CAST

CAST function converts the expression from one data type to another.
Syntax Select Cast(EXPRESSION AS DataType([Length]))
We have following records in our table:

1

We want the datetime part to be converted to varchar type. In order to do the same we will make use of Cast function as shown below:

2

In real life scenario we will be using CAST a lot when we to concatenate two different data type with each other as shown below:

We have following records in a table

ID Name
2 Saillesh
3 VIrender
4 NIhsant

Now we want to display Id,Name, Name+id values in a query. So in order to achieve the same if I simply try to concatenate both these two columns, we will receive an error as shown below:

5
So in order to solve the same, we will make use of Cast function

6

Convert

Convert function is also used to convert the expression from one data type to another data type. Convert function has a different syntax as compared to Cast method.
Convert method first take the datatype than expression and Style which is an optional parameter as shown below:

SELECT TOP 1000 [ID]
      ,[name]
      ,[dob],
	  CONVERT(nvarchar,dob) convertedDate
  FROM [SampleDb].[dbo].[dob]

6

Style parameter in Convert function

We have an optional style parameter in Convert function. In cast function we saw how we were converting the dob of type nvarchar, the problem with CAST function is that we cannot control the format of DOB with CAST, which we can resolve using Convert with Style parameter.

In order to format the DateTime column Microsoft has provided us with styles as shown below:

Style DateFormat
101 mm/dd/yyyy
102 yy.mm.dd
103 dd/mm/yyyy
104 dd.mm.yy
105 dd-mm-yy

In order to check the more Style, you can go and check the MSDN doc for the same.

Date and Time style

So now I want to print the DOB in dd-mm-yyyy format. In order to achieve the same I make use of style parameter in Convert function as shown below:

6

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.