Get Current date and time and its various formats in MS SQL Server

Query for getting current datetime value in SQL Server is :

select getdate()

Output will be in format for the date December 31, 2016:

2016-12-31 20:42:31.930

............................................................................................................

For getting the result in dd/MM/yyyy format query is :

select convert(varchar,getdate(),103)

Output is :

31/12/2016

For without century it is :

select convert(varchar,getdate(),3)

Output is :

31/12/16

For getting the result in dd-MM-yyyy format query is :

select convert(varchar,getdate(),105)

Output is :

31-12-2016

For without century it is :

select convert(varchar,getdate(),5)

Output is :

31-12-16

.............................................................................

For getting the result in  MM/dd/yyyy format query is :

select convert(varchar,getdate(),101)

Output is :

12/31/2016

For without century it is :

select convert(varchar,getdate(),1)

Output is :

12/31/16

For getting the result in MM-dd-yyyy format query is :

select convert(varchar,getdate(),110)

Output is :

12-31-2016

For without century it is :

select convert(varchar,getdate(),10)

Output is :

12-31-16

..............................................................................

For getting the result in  dd mon yyyy format query is :

select convert(varchar,getdate(),106)

Output is :

31 Dec 2016

For without century it is :

select convert(varchar,getdate(),6)

Output is :

31 Dec 16

..............................................................................

For getting the result in  mon dd,  yyyy format query is :

select convert(varchar,getdate(),107)

Output is :

Dec 31, 2016

For without century it is :

select convert(varchar,getdate(),7)

Output is :

Dec 31, 16

.....................................................................................

For getting the result in  dd mon yyyy hh:mi:ss:mmm (24h) format query is :

select convert(varchar,getdate(),113) 

or

select convert(varchar,getdate(),13) 

Output is :

31 Dec 2016 14:48:31:893