未记录的CONVERT样式 - datetime 23 [英] undocumented CONVERT styles - datetime 23

问题描述

最近我偶然发现了 CONVERT 函数样式23，它非常方便，因为它给你的日期格式为$ code> yyyy-mm-dd 。问题是msdn中没有记录！ （在CONVERT上的F1后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?

推荐答案

基于我10年前写的常见问题解答，还有很多其他人从文档（也许从2005年以来已经介绍过，自从我尝试探索以来已经有一段时间了）：

Based largely on a FAQ I wrote 10 years ago, 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; 

当然，与许多无证件一样，使用秘密的风险自担风险。您应该标记使用它们的模块，以便您可以在升级之前对其进行测试 - 它们不会是升级顾问，最佳做法分析器，弃用跟踪事件，扩展事件等将会接收并告诉您的事情，由于Microsoft可以自由删除任何未记录的功能/语法（尽管我发现很不可能删除任何这些功能/语法，即使他们不感兴趣记录它们）。如果您有部署Service Pack /升级的测试服务器，则在执行任何此类升级后运行此代码会告诉您，此处使用的任何样式是否已被删除。所以您可能希望将此代码保存在某个地方，并且只包含您主动使用的无证样式号码。

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.

这篇关于未记录的CONVERT样式 - datetime 23的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！