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

No comments: