Articles in categories

Articles

SQL Server Date Formats

Whenever I want to format the Sql Server date field to a specific format I had to search the web how to do it.
Now I've made myself a list with the most frequent date formats and their sql syntax. It includes an example of what the output will be.

The Transact-SQL (T-SQL) CONVERT() function is used to convert data between different types. When converting a DATETIME value to a VarChar string value a style code may be applied. See the list below for the code to use and the output format.
MSDN: http://msdn.microsoft.com/en-us/library/ms187928.aspx
Sql date time functions: http://msdn.microsoft.com/en-us/library/ms186724.aspx

-- DateTime formatting in SQL Server

SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
-- Jul 30 2012  7:59PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
-- 07/30/12

SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
-- 07/30/2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
-- 12.07.30

SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
-- 2012.07.30

SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
-- 30/07/12

SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
-- 30/07/2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
-- 30.07.12

SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
-- 30.07.2012

SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
-- 30 Jul 12

SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
-- 30 Jul 2012

SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
-- Jul 30, 12

SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
-- Jul 30, 2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
-- 20:06:13

SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
-- Jul 30 2012  8:06:13:077PM

SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
-- 07-30-12

SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
-- 07-30-2012

SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
-- 12/07/30

SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
-- 2012/07/30

SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
-- 120730

SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
-- 20120730

SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
-- 30 Jul 2012 20:07:36:890

SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
-- 20:07:36:890

SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
-- 2012-07-30 20:08:59

SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
-- 2012-07-30 20:08:59.333

SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
-- 2012-07-30T20:08:59.333

SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
-- 12/09/1433  8:08:59:333PM