Format Date to specific display format is common scenario in SQL Server.

CONVERT() is the fuction in SQL by which we can convert Dates into varity of formats.

General syntax is, CONVERT(data_type(length),data_to_be_converted,style)

Below examples with output shows how to get dates in all these formats…..

-- Day first

SELECT CONVERT(VARCHAR(12),GETDATE(), 103) -- 25/11/2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 105) -- 25-11-2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 104) -- 25.11.2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 106) -- 25 Nov 2011

-- Month first

SELECT CONVERT(VARCHAR(12),GETDATE(), 101) -- 11/25/2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 110) -- 11-25-2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 100) -- Nov 25 2011

SELECT CONVERT(VARCHAR(12),GETDATE(), 107) -- Nov 25, 2011

-- Year first

SELECT CONVERT(VARCHAR(12),GETDATE(), 102) -- 2011.11.25

SELECT CONVERT(VARCHAR(12),GETDATE(), 111) -- 2011/11/25

SELECT CONVERT(VARCHAR(12),GETDATE(), 112) -- 20111125

-- Time only

SELECT CONVERT(VARCHAR(12),GETDATE(), 108) -- 06:21:42

SELECT CONVERT(VARCHAR(12),GETDATE(), 114) -- 06:21:51:610

--Only One

SELECT DATENAME(MONTH, GETDATE()) -- November

SELECT DATENAME(DAY,GETDATE()) -- 25

SELECT DATENAME(YEAR, GETDATE()) --2011

Thank You.