Build a cheat sheet for SQL Server date and time formats

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:

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

• See these related tips and other resources:
  • Date and Time Conversions Using SQL Server
  • SQL Server 2008 Date and Time Data Types
  • Determine SQL Server Date and Time with DATEPART and DATENAME Functions
  • Custom Date and Time Format Strings (MSDN)
  • Conversion Functions (Transact-SQL) (MSDN)
  • FORMAT (Transact-SQL) (MSDN)
  • FORMAT() is nice and all, but…

Aaron Bertrand

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He also blogs at sqlblog.org.

• MSSQLTips Awards: Author of the Year – 2016, 2023 | Leadership (200+ tips) – 2022