undocumented CONVERT styles - datetime 23

问题:

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?

回答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.

回答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)

回答3:

Per both old² and newer³ versions of the "CAST and CONVERT" article, several styles including 99 and below are nondeterministic. Some people to say the least, would consider that unsafe.

I also concur with Aaron that something being undocumented probably means they don't expect people to be aware of it or use it, hence arbitrary removal/defect at some point should not be a surprise to us.

Regarding conversion nondeterminism, citing the newer article version, under "Certain datetime conversions are nondeterministic":

The following table [list] lists the styles for which the string-to-datetime conversion is nondeterministic.

• All styles below 100¹
• 106
• 107,
• 109,
• 113,
• 130.

¹ With the exception of styles 20 and 21

Also from the same article, under "Date and Time Styles", this less-visible quote seems to repeat the same list, with the addition of 100:

¹ These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

_{(2) Already linked in question.
(3) 2017-Aug-27.}