undocumented CONVERT styles - datetime 23

Question

Recently I stumbled upon CONVERT function style 23, which is very handy as it gives you DATE in format yyyy-mm-dd. The problem is that it's not documented in msdn! (link from SSMS help after F1 on CONVERT: http://msdn.microsoft.com/en-us/library/ms187928%28SQL.105%29.aspx). Example:

select convert( date ,'2012-01-30', 23)
select convert(varchar(255), getdate(), 23)

This style is very useful and I've been missing it, but my concerns are: - Is it safe to use? Is it deprecated or sneak in by mistake and may be removed in future editions / updates? - Does anybody know of other hidden styles?

Answer 1

Based largely on this article, there are plenty of others missing from the docs (and maybe more have been introduced since 2005, been a while since I tried exploring):

--DROP TABLE dbo.DateTimeStyles;
CREATE TABLE dbo.DateTimeStyles
(  
    styleID TINYINT PRIMARY KEY,  
    outputLength TINYINT, 
    outputSyntax AS (CONVERT(VARCHAR(255), 'CONVERT(CHAR(' 
        + RTRIM(outputLength) + '), CURRENT_TIMESTAMP, ' 
        + RTRIM(styleID) + ')')), 
    outputSample VARCHAR(255)
); 
        
INSERT dbo.DateTimeStyles(styleID, outputLength) 
VALUES (0,   19 ), (1,   8  ), (2,   8  ), (3,   8  ),
       (4,   8  ), (5,   8  ), (6,   9  ), (7,   10 ),
       (8,   8  ), (9,   26 ), (10,  8  ), (11,  8  ),
       (12,  6  ), (13,  24 ), (14,  12 ), (20,  19 ), 
       (21,  23 ), (22,  20 ), (23,  10 ), (24,  8  ),
       (25,  23 ), (100, 19 ), (101, 10 ), (102, 10 ),  
       (103, 10 ), (104, 10 ), (105, 10 ), (106, 11 ),  
       (107, 12 ), (108, 8  ), (109, 26 ), (110, 10 ),  
       (111, 10 ), (112, 8  ), (113, 24 ), (114, 12 ),  
       (120, 19 ), (121, 23 ), (126, 23 ), (127, 23 ),
       (130, 32 ), (131, 25 );

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += N'UPDATE dbo.DateTimeStyles 
        SET outputSample = ' + outputSyntax + ' 
        WHERE styleID = ' + RTRIM(StyleID) + ';'
    FROM dbo.DateTimeStyles; 

EXEC sp_executesql @sql;

SELECT styleID, outputSyntax, outputSample
  FROM dbo.DateTimeStyles
  ORDER BY styleID; 

Of course, as with many undocumented things, use the "secret" ones at your own risk. You should mark the modules where you use them, so that you can test them prior to upgrades - they won't be things the upgrade advisor, best practices analyzer, deprecation trace events, extended events etc. will pick up and tell you about, since Microsoft is free to remove any undocumented features/syntax at their own discretion (though I find it highly unlikely they will ever remove any of these, even if they aren't interested in documenting them). If you have a test server where you deploy service packs / upgrades, running this code there after any such upgrade will tell you if any of the styles used here have been removed. So you may want to save this code somewhere and only include the undocumented style numbers you actively use.

Answer 2

As for the specific case of style 23...I'd suggest not using it since there's such a simple documented alternative:

SELECT CONVERT(CHAR(10),GETDATE(),120)