从 SQL Server DateTime 数据类型中仅返回日期的方法

技术背景

在 SQL Server 中，DateTime 数据类型同时包含日期和时间信息。但在某些场景下，我们只需要日期部分，例如统计每日的销售数据等。因此，需要找到合适的方法从 DateTime 数据类型中提取日期。

实现步骤

SQL Server 2008 之前

在 SQL Server 2008 之前，没有专门的 DATE 数据类型，可使用 DATEADD 和 DATEDIFF 函数组合来实现：

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

示例：

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

该方法无需进行 varchar 与 datetime 之间的转换，也无需考虑区域设置。

SQL Server 2008 及以后

从 SQL Server 2008 开始，引入了 DATE 数据类型，可以使用 CONVERT 或 CAST 函数：

-- 使用 CONVERT 函数
SELECT CONVERT(date, GETDATE())
-- 使用 CAST 函数
SELECT CAST(GETDATE() as date)

SQL Server 2012 及以上

SQL Server 2012 及以上版本可以使用 FORMAT 函数，该函数支持指定日期格式和区域设置：

DECLARE @d DATETIME = '10/01/2011';
SELECT FORMAT ( @d, 'd', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'd', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'd', 'zh-cn' ) AS 'Simplified Chinese (PRC) Result'; 

SELECT FORMAT ( @d, 'D', 'en-US' ) AS 'US English Result'
      ,FORMAT ( @d, 'D', 'en-gb' ) AS 'Great Britain English Result'
      ,FORMAT ( @d, 'D', 'de-de' ) AS 'German Result'
      ,FORMAT ( @d, 'D', 'zh-cn' ) AS 'Chinese (Simplified PRC) Result';

核心代码

使用 DATEADD 和 DATEDIFF

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

使用 CONVERT 转换为 DATE 类型

SELECT CONVERT(date, GETDATE())

使用 CAST 转换为 DATE 类型

SELECT CAST(GETDATE() as date)

使用 FORMAT 函数

SELECT FORMAT(GETDATE(), 'd', 'en-US')

最佳实践

• 性能优先：如果对性能有较高要求，推荐使用 DATEADD 和 DATEDIFF 组合或 CAST 转换为 DATE 类型的方法，因为它们的性能相对较好。
• 格式需求：如果需要特定的日期格式，可以使用 CONVERT 函数并指定格式代码，或者使用 FORMAT 函数。
• 数据层与表示层分离：尽量在表示层进行日期格式的转换，避免在数据层进行过多的格式转换，以减少数据库的开销和维护数据的完整性。

常见问题

性能问题

CONVERT 转换为 varchar 的方法性能相对较差，尤其是在处理大量数据时。可以使用 DATEADD 和 DATEDIFF 组合或 CAST 转换为 DATE 类型的方法来提高性能。

格式代码问题

使用 CONVERT 函数时，需要记住不同的格式代码。以下是一些常见的日期格式代码：

DateFormatCode	Format
1	[MM/DD/YY]
2	[YY.MM.DD]
3	[DD/MM/YY]
4	[DD.MM.YY]
5	[DD-MM-YY]
6	[DD MMM YY]
7	[MMM DD,YY]
10	[MM-DD-YY]
11	[YY/MM/DD]
12	[YYMMDD]
23	[yyyy-mm-dd]
101	[MM/DD/YYYY]
102	[YYYY.MM.DD]
103	[DD/MM/YYYY]
104	[DD/MM/YYYY]
105	[DD/MM/YYYY]
106	[DD MMM YYYY]
107	[MMM DD,YYYY]
110	[MM-DD-YYYY]
111	[YYYY/MM/DD]
112	[YYYYMMDD]

数据完整性问题

在数据层进行日期格式转换可能会破坏数据的完整性，导致后续操作出现问题。因此，建议在表示层进行格式转换。