SQL Server- CONVERT() Function for Date formatting

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.