问将TSQL日期格式化为mm-dd-yyyy

EN

您误解了日期值是如何在数据库中工作的。没有人类可读的格式.当您看到DateTime或DateTime2值格式化为yyyy-mm-dd时，为了方便起见，您看到的是调试器或查询工具所显示的内容；数据库中使用的实际值是二进制的，而不是人类可读的，其目的是提高存储、索引和日期算法的效率。

如果需要查看特定格式的值，则必须将其作为查询的一部分将其convert()或format()为varchar系列中的类型。或者，更好的是，允许应用程序代码/报告工具为执行此操作，并只返回原始值。

我还看到这些日期可能最初存储在varchar或nvarchar列中的指示。如果是这样的话，是模式设计中的一个主要的缺陷。如果您能够首先使用DateTime家族的一个类型来存储这些值，那么将获得显著的性能好处，并在今后的道路上省去一些麻烦。

考虑到这一点，并且由于不清楚您从何处开始，让我们按照偏好顺序来看五种场景：

该列已经使用了来自DateTime家族的类型，您可以让应用程序/报告工具处理格式

这对您在模式中使用真正的DateTime值很有好处。这就是我们希望看到的。更好的是，突然之间，SQL中的所有内容都变得非常简单，问题中的整个片段都简化为这样：

 ai.[Due Date] AS [TD]

该列已经使用了来自DateTime系列的类型，但客户端系统无法格式化

这还是挺不错的。模式仍然正常，在本例中，我们仍然可以稍微简化原始代码：

COALESCE(
    CASE WHEN ai.[Due Date] < GETDATE() THEN '[due] ' ELSE '' END 
    + FORMAT(ai.[Due Date], 'MM-dd-yyyy')
, ' ') AS [TD]

该列使用varchar系列中的a类型，但可以将其修复为使用DateTime2。

我在这里说“修复”，因为现在模式确实被破坏了。但没关系:你可以修好它。就这么做吧。然后使用前一个场景中的代码。

该列使用来自varchar家族的a类型，您无法修复它，但至少原始数据总是使用语义‘yyyy’格式。

真扫兴。你被一个坏了的模式困住了。但是，我们至少可以利用格式良好的数据，通过在表达式上使用cast/来匹配列，从而使get_date()更加高效，而不是像现在这样相反：

WHEN ai.[Due Date] < CONVERT(varchar, GETDATE(), 120)

现在我们做的是字符串比较，而不是日期比较，这通常比较慢，而且是错误的。但是，由于数据中的格式很好，所以我们可以不使用它，而且节省的好处是，我们只需要转换一个get_date()值，而不是我们拥有的每一行。此外，这样，列上的任何索引都仍然有效。问题上的代码片段将无法对[Due Date]列使用任何索引。我知道这是一个SELECT子句，但是对于一般情况来说，这是值得记住的。

这个场景的完整解决方案现在如下所示：

COALESCE(
    CASE WHEN ai.[Due Date] < CONVERT(varchar, GETDATE(), 120) THEN '[due] ' ELSE '' END
    + FORMAT(CAST(ai.[Due Date]) AS Date), 'MM-dd-yyyy')
, ' ') AS [TD]

同样，如果无法将原始列数据转换为格式，则只有才会执行此操作。这才是你真正想要的。

该列使用varchar系列中的a类型，您无法修复它，而且格式不语义或不一致

哦，天哪。这是你不想去的地方。如果您不能做任何其他事情，那么至少看看您是否可以开始在您的列中获得一致的语义值。在这一点上，我们不得不为几乎每个查询在我们拥有的每一行(可能不止一次)上做额外的工作。我们开始：

COALESCE(
    CASE WHEN CAST(ai.[Due Date] AS DATETIME2)  < GETDATE() THEN '[due] ' ELSE '' END 
    + FORMAT(CAST(ai.[Due Date] AS DATETIME2), 'MM-dd-yyyy')
, ' ') AS [TD]

代码看起来与其他选项没有太大的不同，但是性能特征会非常不同.可能多个数量级更糟。

记住:由于国际化和时区问题，字符串和日期之间的转换非常缓慢和昂贵。在所有查询中，尽可能避免这样做。

传统的方法是使用convert()

convert(varchar(10), ai.[Due Date], 110)

一种更通用的方法是使用format()

select format(ai.[Due Date], 'dd-MM-yyyy')