convert datetime to various date formats in sql

convert datetime to various date format using convert function in sql.

Suppose you have a date :
select getdate()

o/p: 2013-04-18 17:27:54.167

Now how to convert to various date format:

Example 1:

select getdate()
select CONVERT(VARCHAR(20),getdate(),100)
select CONVERT(VARCHAR(20),getdate(),101)
select CONVERT(VARCHAR(20),getdate(),103)
select CONVERT(VARCHAR(20),getdate(),106)
select CONVERT(VARCHAR(20),getdate(),110)
select CONVERT(VARCHAR(20),getdate(),112)

Example 2:

declare @date as datetime = '12-April-2013'
select @date
select CONVERT(VARCHAR(20),@date,100)
select CONVERT(VARCHAR(20),@date,101)
select CONVERT(VARCHAR(20),@date,103)
select CONVERT(VARCHAR(20),@date,106)
select CONVERT(VARCHAR(20),@date,110)
select CONVERT(VARCHAR(20),@date,112)


where 100,101,102 etc are the date format code.

for more date format codes refer.