Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Build a cheat sheet for SQL Server date and time formats


By:   |   Last Updated: 2015-10-13   |   Comments (10)   |   Related Tips: More > Dates

Problem

I'm a forgetful person, and I often have to look up which style number for convert will be easiest to achieve a certain output. Now, to be completely honest, I am mostly against doing any type of string formatting inside the database - this is generally better handled by the presentation tier. But, I recognize that sometimes this is done in a view that serves multiple applications or reports, or the powers that be have simply dictated that the database will be doing this work. In these cases, it can be handy to have a "cheat sheet" that shows you all of the output styles available through the CONVERT() function.

Solution

I've found that it is not too difficult to build a script that can be re-used to generate such a cheat sheet. Let's first determine all the style numbers that are valid. We can do this by attempting conversion to all styles between 0 and 255, and insert into a #temp table the ones that don't yield an error:

SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX), @v VARCHAR(30), @d DATETIME2(7);

SET @sql = N'';

-- a random date/time, making sure no single digits for any
-- date parts as these can truncate length of output:

SET @d = '2015-12-31T22:25:59.7901245';

CREATE TABLE #s(style VARCHAR(3));

DECLARE @s INT = 0;
WHILE @s <= 255
BEGIN
  BEGIN TRY;
    SET @sql = N'SELECT @v = CONVERT(VARCHAR(30), @d, ' + RTRIM(@s) + ');';
	EXEC sys.sp_executesql @sql, N'@v VARCHAR(30), @d DATETIME2(7)', @v, @d;
    INSERT #s(style) VALUES(@s); 
  END TRY
  BEGIN CATCH;
    SET @sql = N'';
  END CATCH;
  SET @s = @s + 1;
END

Now, the #temp table is populated with all of the styles that produced valid output and no error (42 in total, including styles 130 and 131 which have Hijri output). Note that this code is meant to work on all versions of SQL Server 2005 and up; for 2012 and up, you could do something much simpler, like:

SET @sql = N'';

;WITH x(rn) AS 
(
  SELECT TOP (256) CONVERT(VARCHAR(3), ROW_NUMBER() OVER (ORDER BY name) - 1)
  FROM sys.all_objects ORDER BY name
)
SELECT @sql = @sql + N'INSERT #s SELECT ' + rn + ' FROM 
  (SELECT n = TRY_CONVERT(VARCHAR(30),@d,' + rn + ')) AS x 
  WHERE n IS NOT NULL;' FROM x;

EXEC sys.sp_executesql @sql, N'@d DATETIME2(7)', @d;

Next, we can generate some dynamic SQL that will output each style number, the expression that is used to apply that style to the conversion of a date/time variable, and the actual output of that expression.

SET @sql = N'';

SELECT @sql = @sql + N' UNION ALL SELECT [style #] = ' 
  + style + ', expression = N''CONVERT(CHAR(''
    +RTRIM(LEN(CONVERT(VARCHAR(30), @d, ' + style + ')))
	+''), @d, ' + style + ')'',
	[output] = CONVERT(VARCHAR(30), @d, ' + style + ')'
FROM #s;

SET @sql = STUFF(@sql, 1, 11, N'') + N';';

EXEC sys.sp_executesql @sql, N'@d DATETIME2(7)', @d;

Full output:

style #expressionoutput
0CONVERT(CHAR(19), @d, 0)Dec 31 2015 10:25PM
1CONVERT(CHAR(8), @d, 1)12/31/15
2CONVERT(CHAR(8), @d, 2)15.12.31
3CONVERT(CHAR(8), @d, 3)31/12/15
4CONVERT(CHAR(8), @d, 4)31.12.15
5CONVERT(CHAR(8), @d, 5)31-12-15
6CONVERT(CHAR(9), @d, 6)31 Dec 15
7CONVERT(CHAR(10), @d, 7)Dec 31, 15
8CONVERT(CHAR(8), @d, 8)22:25:59
9CONVERT(CHAR(30), @d, 9)Dec 31 2015 10:25:59.7901245PM
10CONVERT(CHAR(8), @d, 10)12-31-15
11CONVERT(CHAR(8), @d, 11)15/12/31
12CONVERT(CHAR(6), @d, 12)151231
13CONVERT(CHAR(28), @d, 13)31 Dec 2015 22:25:59.7901245
14CONVERT(CHAR(16), @d, 14)22:25:59.7901245
20CONVERT(CHAR(19), @d, 20)2015-12-31 22:25:59
21CONVERT(CHAR(27), @d, 21)2015-12-31 22:25:59.7901245
22CONVERT(CHAR(20), @d, 22)12/31/15 10:25:59 PM
23CONVERT(CHAR(10), @d, 23)2015-12-31
24CONVERT(CHAR(8), @d, 24)22:25:59
25CONVERT(CHAR(27), @d, 25)2015-12-31 22:25:59.7901245
100CONVERT(CHAR(19), @d, 100)Dec 31 2015 10:25PM
101CONVERT(CHAR(10), @d, 101)12/31/2015
102CONVERT(CHAR(10), @d, 102)2015.12.31
103CONVERT(CHAR(10), @d, 103)31/12/2015
104CONVERT(CHAR(10), @d, 104)31.12.2015
105CONVERT(CHAR(10), @d, 105)31-12-2015
106CONVERT(CHAR(11), @d, 106)31 Dec 2015
107CONVERT(CHAR(12), @d, 107)Dec 31, 2015
108CONVERT(CHAR(8), @d, 108)22:25:59
109CONVERT(CHAR(30), @d, 109)Dec 31 2015 10:25:59.7901245PM
110CONVERT(CHAR(10), @d, 110)12-31-2015
111CONVERT(CHAR(10), @d, 111)2015/12/31
112CONVERT(CHAR(8), @d, 112)20151231
113CONVERT(CHAR(28), @d, 113)31 Dec 2015 22:25:59.7901245
114CONVERT(CHAR(16), @d, 114)22:25:59.7901245
120CONVERT(CHAR(19), @d, 120)2015-12-31 22:25:59
121CONVERT(CHAR(27), @d, 121)2015-12-31 22:25:59.7901245
126CONVERT(CHAR(27), @d, 126)2015-12-31T22:25:59.7901245
127CONVERT(CHAR(27), @d, 127)2015-12-31T22:25:59.7901245
130CONVERT(CHAR(30), @d, 130)20 ???? ????? 1437 10:25:59.79
131CONVERT(CHAR(29), @d, 131)20/03/1437 10:25:59.7901245PM

Create Date / Time Formats in HTML format

Your next question is probably, what if I want to present this output on a web page, and want to generate an HTML table like the one above? No problem, we can do that with a few minor changes to the dynamic SQL:

SET @sql = N'SELECT ''<table><tr><th>style #<th>expression<th>output</tr>''';

SELECT @sql = @sql + N' UNION ALL SELECT ''<tr><td>' 
  + style + '<td>CONVERT(CHAR(''
    +RTRIM(LEN(CONVERT(VARCHAR(30), @d, ' + style + ')))
	+''), @d, ' + style + ')<td>'' + 
	CONVERT(VARCHAR(30), @d, ' + style + ')+''</tr>'''
FROM #s;

SET @sql = @sql + ' UNION ALL SELECT ''</table>'';';

EXEC sys.sp_executesql @sql, N'@d DATETIME2(7)', @d;

Truncated output:

Using FORMAT to format dates

In any case, that's a pretty exhaustive set, but there may be some formats you want that are not represented above. While I typically do not recommend the FORMAT() function because of the demonstrable overhead of using the CLR, it can be used in a pinch when you have no other options. FORMAT() was introduced in SQL Server 2012, and behaves similarly to its C# equivalent (documented here). So, you can do things like this without having to memorize any style numbers:

SELECT FORMAT(@d, 'MMMM, yyyy'),       --> December, 2015
       FORMAT(@d, 'MMM-yyyy'),         --> Dec-2015
       FORMAT(@d, 'dddd MMMM d'),      --> Thursday December 31
       FORMAT(@d, 'yyyy -> MM -> dd'), --> 2015 -> 12 -> 31
       FORMAT(@d, 'h\h, m\m');         --> 10h, 25m

But I will stress again that, while this may seem more convenient than cobbling together multiple outputs from CONVERT(), DATEPART(), or DATENAME(), the performance impact can be significant at high volumes.

Don't forget to clean up:

DROP TABLE #s;

Summary

Memorizing meaningless style numbers can be a pain, but with a little creative thinking, you can build yourself a cheat sheet in no time. And you can repeat this process with different date inputs to see differences in output, or on different versions of SQL Server (since supported style numbers may vary by version). Or you could just bookmark this page. :-)

Next Steps


Last Updated: 2015-10-13


next webcast button


next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a Product Manager at SentryOne, with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and serves as a community moderator for the Database Administrators Stack Exchange.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, April 04, 2016 - 9:39:18 PM - Kris Maly Back To Top

 Awesome!

Do create such articles which will help developers.

Thanks a lot

 


Wednesday, October 14, 2015 - 10:02:42 AM - Andrew Back To Top

@Aaron, yes I'm SQL 2008 R2.  :(

 

Thank you!


Wednesday, October 14, 2015 - 9:55:27 AM - Aaron Bertrand Back To Top

@Brock - yes, you *could* use FORMAT(), but as I mentioned in the article (see the section "Using FORMAT() to format dates"), I recommend against it, because it is usually at least twice as slow as CONVERT. See this post: http://sqlperformance.com/2015/06/t-sql-queries/format-is-nice-and-all-but

@Andrew - sounds like you are trying to run the code "for 2012 and up" on a version earlier than 2012.


Wednesday, October 14, 2015 - 9:51:01 AM - Andrew Back To Top

Excellent Article thank you!

 


Wednesday, October 14, 2015 - 9:07:38 AM - Brock Back To Top

If you're using SQL Server 2012 or newer, you can use the more intuitive FORMAT() function instead of CONVERT()..

select format(sysdatetimeoffset(), 'yyyy-MM-dd')

You can format more than just dates as well.  Applies to almost any data type.

See https://msdn.microsoft.com/en-us/library/hh213505.aspx for more info.


Tuesday, October 13, 2015 - 5:54:26 PM - Chris Back To Top

Excellent article. Thanks for sharing. Have already bookmarked this.


Tuesday, October 13, 2015 - 5:51:59 PM - Andrew Back To Top

I try running the code.

were is building the top (256) records there is a Try_convert and not sure this is valid.  I get an error.

 

So I take the try_convert and replace it with just convert.

I set the @d with Getdate()

I still get an error like the following:

Msg 281, Level 16, State 1, Line 31

 

15 is not a valid style number when converting from datetime2 to a character string.

 

Any advice please?

Thank you,

Andrew


Tuesday, October 13, 2015 - 1:38:53 PM - John Shahan Back To Top

Much thanks for sharing this, Aaron.  Well done and it will be put to good use.

 


Tuesday, October 13, 2015 - 9:54:50 AM - Aaron Bertrand Back To Top

Scott, I agree (see my opening paragraph again). However, people are going to keep doing it for a variety of real reasons, whether we like it or not.


Tuesday, October 13, 2015 - 9:13:19 AM - Scott Brickey Back To Top

while formatting in SQL is pretty common, I must recommend AGAINST it for all but a VERY few cases.

Formatting is something that the UI is supposed to be responsible for. I wouldn't expect SQL to handle formatted numbers ("$1,000,000") during an insert, and I shouldn't have to expect it during an output.

Every UI platform (Classic ASP, PHP, C#, Reporting Services, Excel, Tableau, BusinessObjects) is going to allow you to format the number. And more importantly, to format it to YOU, which means regional awareness such as knowing whether to use periods or commas to separate whole numbers from the decimals, or adjusting the date to be timezone (and daylight savings time) specific (admittedly, reporting systems often lack some of the regional awareness, but there are reasons for this). Examples of the different number formats are found on Wikipedia at : https://en.wikipedia.org/wiki/Decimal_mark#Examples_of_use

Additionally, these platforms may in fact be CONFUSED by formatted data, and thus misinterpret its results. Consider a database view which formats a number as "##,###.000" (as used by US and others) being queried by a platform in Germany (which uses the commas and periods opposite of the US). The platform, upon reading the value "10.125" may INTERPRET the value as 10,125.00 (US).

The only situation that I'll half accept as a reasonable use of formatting in SQL, is when SQL is being used directly to dump data to a separate format (such as BCP'ing from a VIEW to a file that will be transferred to another system). Even in this space, a proper ETL solution such as SSIS would be a more appropriate location to define and configure such settings.

Formatting in the data is an easy way to cause more problems than it solves, and so I must (respectfully) disagree.


Learn more about SQL Server tools