Some Common DateTime Formats in SQL Server 2005/2008

One of the most commonly asked question in forums/newsgroups/blogs is to format date and time and show it on the UI. Here's a list of some commonly used DateTime Formats that you can use in your projects

SELECT CONVERT(varchar(30), GETDATE(), 1) --   Result - 03/28/09

SELECT CONVERT(varchar(30), GETDATE(), 2) --   Result - 09.03.28

SELECT CONVERT(varchar(30), GETDATE(), 3) --   Result - 28/03/09

SELECT CONVERT(varchar(30), GETDATE(), 4) --   Result - 28.03.09

SELECT CONVERT(varchar(30), GETDATE(), 5) --   Result - 28-03-09

SELECT CONVERT(varchar(30), GETDATE(), 6) --   Result - 28 Mar 09

SELECT CONVERT(varchar(30), GETDATE(), 7) --   Result - Mar 28, 09

SELECT CONVERT(varchar(30), GETDATE(), 8) --   Result - 14:35:50

SELECT CONVERT(varchar(30), GETDATE(), 9) --   Result - Mar 28 2009 2:35:50:637PM

SELECT CONVERT(varchar(30), GETDATE(), 10) -- Result - 03-28-09

SELECT CONVERT(varchar(30), GETDATE(), 11) -- Result - 09/03/28

SELECT CONVERT(varchar(30), GETDATE(), 12) -- Result - 090328

SELECT CONVERT(varchar(30), GETDATE(), 13) -- Result - 28 Mar 2009 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 14) -- Result - 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 101) -- Result - 03/28/2009

SELECT CONVERT(varchar(30), GETDATE(), 102) -- Result - 2009.03.28

SELECT CONVERT(varchar(30), GETDATE(), 103) -- Result - 28/03/2009

SELECT CONVERT(varchar(30), GETDATE(), 104) -- Result - 28.03.2009

SELECT CONVERT(varchar(30), GETDATE(), 105) -- Result - 28-03-2009

SELECT CONVERT(varchar(30), GETDATE(), 106) -- Result - 28 Mar 2009

SELECT CONVERT(varchar(30), GETDATE(), 107) -- Result - Mar 28, 2009

SELECT CONVERT(varchar(30), GETDATE(), 108) -- Result - 14:35:50

SELECT CONVERT(varchar(30), GETDATE(), 109) -- Result - Mar 28 2009 2:35:50:637PM

SELECT CONVERT(varchar(30), GETDATE(), 110) -- Result - 03-28-2009

SELECT CONVERT(varchar(30), GETDATE(), 111) -- Result - 2009/03/28

SELECT CONVERT(varchar(30), GETDATE(), 112) -- Result - 20090328

SELECT CONVERT(varchar(30), GETDATE(), 113) -- Result - 28 Mar 2009 14:35:50:637

SELECT CONVERT(varchar(30), GETDATE(), 114) -- Result - 14:35:50:637

If you are looking out for UTC/GMT function, check this previous post of mine.