One of the things that I always had to look up, in the early days of my SQL querying, was data formats. It got to a point where I just kept a document that I built up from various websites, that contained as many date format conversions as possible. And I would just go to this as a first port of call. And then, if it didn’t have what I needed, I’d search on the internet.
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
— Sample Output: Jan 1 2005 1:29PM
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]
— Sample Output: 11/23/98
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]
— Sample Output: 11/23/1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]
— Sample Output: 72.01.01
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]
— Sample Output: 1972.01.01
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]
— Sample Output: 19/02/72
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]
— Sample Output: 19/02/1972
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]
— Sample Output: 25.12.05
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]
— Sample Output: 25.12.2005
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]
— Sample Output: 24-01-98
SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]
— Sample Output: 24-01-1998
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]
— Sample Output: 04 Jul 06
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]
— Sample Output: 04 Jul 2006
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]
— Sample Output: Jan 24, 98
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]
— Sample Output: Jan 24, 1998
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
— Sample Output: 03:24:53
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
— Sample Output: Apr 28 2006 12:32:29:253PM
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]
— Sample Output: 01-01-06
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]
— Sample Output: 01-01-2006
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]
— Sample Output: 98/11/23
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]
— Sample Output: 1998/11/23
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]
— Sample Output: 980124
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]
— Sample Output: 19980124
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
— Sample Output: 28 Apr 2006 00:34:55:190
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]
— Sample Output: 11:34:23:013
SELECT CONVERT(VARCHAR(19), GETDATE(), 120)
— Sample Output: 1972-01-01 13:42:24
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
— Sample Output: 1972-02-19 06:35:24.489
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
— Sample Output: 1998-11-23T11:25:43:250
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
— Sample Output: 28 Apr 2006 12:39:32:429AM
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
— Sample Output: 28/04/2006 12:39:32:429AM
=========
— Extended Date Formats
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]
— Sample Output: 99-01-24
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]
— Sample Output: 1999-01-24
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
— Sample Output: 08/99
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]
— Sample Output: 12/2005
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]
— Sample Output: 99/08
SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]
— Sample Output: 2005/12
SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]
— Sample Output: July 04, 2006
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]
— Sample Output: Apr 2006
SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]
— Sample Output: February 2006
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month]
— Sample Output: 11 September
SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
— Sample Output: September 11
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]
— Sample Output: 19 February 72
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]
— Sample Output: 11 September 2002
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]
SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
— Sample Output: 12/92
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]
— Sample Output: 05-2006
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
— Sample Output: 92/12
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM] 2006-05
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY]
— Sample Output: 122506
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY]
— Sample Output: 12252006
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY]
— Sample Output: 240702
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY]
— Sample Output: 24072002
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY]
— Sample Output: Sep-02
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY]
— Sample Output: Sep-2002
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY]
— Sample Output: 25-Dec-05
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY]
— Sample Output: 25-Dec-2005
dataprediction.wordpress.com 