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.

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook