CONVERT Dates and Confusion

Right I've been itching to post another Blog and actually contribute something this time.  This isn't something I've learned but something I've taught many times in the past.


It can't be just me but I get asked a lot about the formatting of dates especially for reports.


This is something that is almost always over complicated and misunderstood, one table in BOL covers it.  Search for 'CONVERT function' and the first remark is 'Date and Time Styles'.  This table gives you certainly every date format I have ever had to use and more.  My particular favourite is ISO 112 it confuses the hell out of people when you use it on a form, but that's just my inner geek coming out.


I've included a selection of the most common conversions I have used in the past in the below T-SQL



-- Month first  
SELECT CONVERT(VARCHAR(12),GETDATE(), 101)  -- 11/08/2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 110)  -- 11-08-2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 100)  -- Nov  8 2011 
SELECT CONVERT(VARCHAR(12),GETDATE(), 107)  -- Nov 08, 2011


-- Year first  
SELECT CONVERT(VARCHAR(12),GETDATE(), 102)  -- 2011.11.08
SELECT CONVERT(VARCHAR(12),GETDATE(), 111)  -- 2011/11/08
SELECT CONVERT(VARCHAR(12),GETDATE(), 112)  -- 20111108
   
-- Day first  
SELECT CONVERT(VARCHAR(12),GETDATE(), 103)  -- 08/11/2011 
SELECT CONVERT(VARCHAR(12),GETDATE(), 105)  -- 08-11-2011 
SELECT CONVERT(VARCHAR(12),GETDATE(), 104)  -- 08.11.2011
SELECT CONVERT(VARCHAR(12),GETDATE(), 106)  -- 08 Nov 2011 
   
-- Time only  
SELECT CONVERT(VARCHAR(12),GETDATE(), 108)  -- November
SELECT CONVERT(VARCHAR(12),GETDATE(), 114)  -- 14:36:14:043


SELECT DATENAME(MONTH, GETDATE()) -- September 
SELECT DATENAME(DAY,GETDATE()) -- 8  
SELECT DATENAME(YEAR, GETDATE()) -- 2011  


-- Concatente values  
-- November-8-2011
SELECT DATENAME(MONTH, GETDATE()) + '-' + DATENAME(DAY,GETDATE()) + '-' + DATENAME(YEAR, GETDATE()) 


Enjoy, Chris!