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

>

>Custom Formats:

Date FormatSQL StatementSample Output
YY-MM-DDSELECT 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-DDSELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS
[YYYY-MM-DD] >SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(),
111), '/', '-') AS [YYYY-MM-DD]
2010-01-24
MM/YYSELECT 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/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7)
AS [MM/YYYY]
12/2010
YY/MMSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS
[YY/MM]
10/08
YYYY/MMSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]2010/12
Month DD, YYYY 1SELECT DATENAME(MM, GETDATE()) +
RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD,
YYYY]
July 04, 2010 1
Mon YYYY 1SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113),
4, 8) AS [Mon YYYY]
Apr 2010 1
Month YYYY 1SELECT DATENAME(MM, GETDATE()) + ' ' +
CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]
February 2010 1
DD Month 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' +
DATENAME(MM, GETDATE()) AS [DD Month]
11 September 1
Month DD 1SELECT DATENAME(MM, GETDATE()) + ' ' +
CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]
September 11 1
DD Month YY 1SELECT 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 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' +
DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS
VARCHAR(4)) AS [DD Month YYYY]
11 September 2010 1
MM-YYSELECT 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-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7)
AS [MM-YYYY]
05-2010
YY-MMSELECT 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-MMSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS
[YYYY-MM]
2010-05
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/',
'') AS [MMDDYY]
122510
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101),
'/', '') AS [MMDDYYYY]
12252010
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/',
'') AS [DDMMYY]
240710
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103),
'/', '') AS [DDMMYYYY]
24072010
Mon-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6),
6), ' ', '-') AS [Mon-YY]
Sep-10 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(),
106), 8), ' ', '-') AS [Mon-YYYY]
Sep-2010 1
DD-Mon-YY 1SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ',
'-') AS [DD-Mon-YY]
25-Dec-10 1
DD-Mon-YYYY 1SELECT 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

Tags:

No comments yet.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.