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.