Posted in: Microsoft SQL

SQL 201-Formatting Dates in Microsoft SQL

One of the more frequent questions I get for SQL Server is how to format a DATETIME into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended...

Shannon Lowder

Shannon Lowder

— 5 min read

One of the more frequent questions I get for SQL Server is how to format a DATETIME into a specific date format. Here’s a summary of the different date formats that come standard in SQL Server as part of the CONVERT function. Following the standard date formats are some extended date formats that are often requested by SQL Server developers. It is worth to note that the outputs of these conversions are VARCHAR and not DATETIME. With this in mind, any date comparisons performed after the DATETIME value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.

Date Format SQL Statement Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)
1
SELECT CONVERT(VARCHAR(20), GETDATE(), 100)
 Jan 1 2010 1:29PM 1
MM/DD/YY
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS
[MM/DD/YY]
 11/23/10
MM/DD/YYYY
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS
[MM/DD/YYYY]
 11/23/2010
YY.MM.DD
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS
[YY.MM.DD]
 10.01.01
YYYY.MM.DD
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS
[YYYY.MM.DD]
 2010.01.01
DD/MM/YY
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS
[DD/MM/YY]
 20/02/10
DD/MM/YYYY
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS
[DD/MM/YYYY]
 20/02/2010
DD.MM.YY
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS
[DD.MM.YY]
 25.12.10
DD.MM.YYYY
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS
[DD.MM.YYYY]
 25.12.2010
DD-MM-YY
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS
[DD-MM-YY]
 24-01-10
DD-MM-YYYY
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS
[DD-MM-YYYY]
 24-01-2010
DD Mon YY 1
1
2
SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON
YY]
 04 Jul 06 1
DD Mon YYYY 1
1
2
SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON
YYYY]
 04 Jul 2010 1
Mon DD, YY 1
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD,
YY]
 Jan 24, 10 1
Mon DD, YYYY 1
1
2
SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD,
YYYY]
 Jan 24, 2010 1
HH:MM:SS
1
SELECT CONVERT(VARCHAR(8), GETDATE(), 108)
 03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1
1
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)
 Apr 28 2010 12:32:29:253PM 1
MM-DD-YY
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS
[MM-DD-YY]
 01-01-06
MM-DD-YYYY
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS
[MM-DD-YYYY]
 01-01-2010
YY/MM/DD
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS
[YY/MM/DD]
 10/11/23
YYYY/MM/DD
1
2
SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS
[YYYY/MM/DD]
 2010/11/23
YYMMDD
1
2
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS
[YYMMDD]
 100124
YYYYMMDD
1
2
SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS
[YYYYMMDD]
 20100124
DD Mon YYYY HH:MM:SS:MMM(24h) 1
1
SELECT CONVERT(VARCHAR(24), GETDATE(), 113)
 28 Apr 2010 00:34:55:200 1
HH:MI:SS:MMM(24H)
1
2
SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS
[HH:MI:SS:MMM(24H)]
 11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)
1
SELECT CONVERT(VARCHAR(20), GETDATE(), 120)
 2010-01-01 13:42:24
YYYY-MM-DD HH:MI:SS.MMM(24h)
1
SELECT CONVERT(VARCHAR(23), GETDATE(), 121)
 2010-02-20 06:35:24.489
YYYY-MM-DDTHH:MM:SS:MMM
1
SELECT CONVERT(VARCHAR(23), GETDATE(), 126)
 2010-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1
1
SELECT CONVERT(VARCHAR(26), GETDATE(), 130)
 28 Apr 2010 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM
1
SELECT CONVERT(VARCHAR(25), GETDATE(), 131)
 28/04/2010 12:39:32:429AM

>

>Custom Formats:
Date Format SQL Statement Sample Output
YY-MM-DD
1
2
3
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120),
3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(),
11), '/', '-') AS [YY-MM-DD]
 10-01-24
YYYY-MM-DD
1
2
3
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS
[YYYY-MM-DD] >SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(),
111), '/', '-') AS [YYYY-MM-DD]
 2010-01-24
MM/YY
1
2
3
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS
[MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5)
AS [MM/YY]
 08/10
MM/YYYY
1
2
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7)
AS [MM/YYYY]
 12/2010
YY/MM
1
2
SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS
[YY/MM]
 10/08
YYYY/MM SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM] 2010/12
Month DD, YYYY 1
1
2
3
SELECT DATENAME(MM, GETDATE()) +
RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD,
YYYY]
 July 04, 2010 1
Mon YYYY 1
1
2
SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113),
4, 8) AS [Mon YYYY]
 Apr 2010 1
Month YYYY 1
1
2
SELECT DATENAME(MM, GETDATE()) + ' ' +
CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]
 February 2010 1
DD Month 1
1
2
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' +
DATENAME(MM, GETDATE()) AS [DD Month]
 11 September 1
Month DD 1
1
2
SELECT DATENAME(MM, GETDATE()) + ' ' +
CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
 September 11 1
DD Month YY 1
1
2
3
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' +
DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS
VARCHAR(4)), 2) AS [DD Month YY]
 19 February 10 1
DD Month YYYY 1
1
2
3
SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' +
DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS
VARCHAR(4)) AS [DD Month YYYY]
 11 September 2010 1
MM-YY
1
2
3
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS
[MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5)
AS [MM-YY]
 12/10
MM-YYYY
1
2
SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7)
AS [MM-YYYY]
 05-2010
YY-MM
1
2
3
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS
[YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3,
5) AS [YY-MM]
 10/12
YYYY-MM
1
2
SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS
[YYYY-MM]
 2010-05
MMDDYY
1
2
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/',
'') AS [MMDDYY]
 122510
MMDDYYYY
1
2
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101),
'/', '') AS [MMDDYYYY]
 12252010
DDMMYY
1
2
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/',
'') AS [DDMMYY]
 240710
DDMMYYYY
1
2
SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103),
'/', '') AS [DDMMYYYY]
 24072010
Mon-YY 1
1
2
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6),
6), ' ', '-') AS [Mon-YY]
 Sep-10 1
Mon-YYYY 1
1
2
SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(),
106), 8), ' ', '-') AS [Mon-YYYY]
 Sep-2010 1
DD-Mon-YY 1
1
2
SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ',
'-') AS [DD-Mon-YY]
 25-Dec-10 1
DD-Mon-YYYY 1
1
2
SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), '
', '-') AS [DD-Mon-YYYY]
 25-Dec-2010 1

1 To make the month name in upper case, simply use the UPPER
string function.

References: MSDN

Posted via email from slowder’s posterous

Related posts:

  1. Microsoft Exam 70-433 (part 3)
  2. sql server 2000 + How to display date time
  3. Microsoft Exam 70-433
  4. T-SQL Challenge: Find Days of the Week
  5. Microsoft Continues to Support PHP

Read more