未记录的 CONVERT 样式 - datetime 23

2022-01-16 00:00:00 datetime string-formatting tsql sql-server sql-server-2008-r2

最近我偶然发现了 CONVERT 函数样式 23，它非常方便，因为它以 yyyy-mm-dd 格式为您提供 DATE.问题是它没有记录在 msdn 中！(在 F1 上 CONVERT 后来自 SSMS 帮助的链接:http://msdn.microsoft.com/en-us/library/ms187928%28SQL.105%29.aspx).示例:

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?

推荐答案

主要基于 这篇文章，文档中还缺少很多其他内容(也许自 2005 年以来已经引入了更多内容，一直自从我尝试探索以来已经有一段时间了):

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.

相关文章