SQL Server中如何将分秒毫秒置零及实现日期格式转换

Hey there! Let's break down your two SQL Server date handling needs step by step, with practical examples you can use right away.

If you need to truncate a datetime value to the nearest hour (stripping out minutes, seconds, and milliseconds), here are three reliable methods depending on your SQL Server version:

• 方法1：DATEADD + DATEDIFF组合（兼容所有SQL Server版本）
  This is the most widely compatible approach, working across every supported version of SQL Server. It calculates the number of hours since the "base" datetime (1900-01-01 00:00:00) and adds that back to the base time:

  DECLARE @OriginalDateTime DATETIME = '2024-05-20 14:35:42.123';
  SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, @OriginalDateTime), 0) AS TruncatedToHour;
  -- 输出结果：2024-05-20 14:00:00.000
  

• 方法2：CAST到DATE类型再拼接小时（直观易懂）
  Another approach is to first extract the date portion (which removes all time components), then add back the hour from the original datetime:

  DECLARE @OriginalDateTime DATETIME = '2024-05-20 14:35:42.123';
  SELECT CAST(CAST(@OriginalDateTime AS DATE) AS DATETIME) + DATEADD(HOUR, DATEPART(HOUR, @OriginalDateTime), 0) AS TruncatedToHour;
  -- 输出结果：2024-05-20 14:00:00.000
  

• 方法3：DATETRUNC函数（SQL Server 2022及以上版本）
  For newer SQL Server versions, Microsoft introduced the DATETRUNC function which makes truncation straightforward and readable:

  DECLARE @OriginalDateTime DATETIME = '2024-05-20 14:35:42.123';
  SELECT DATETRUNC(HOUR, @OriginalDateTime) AS TruncatedToHour;
  -- 输出结果：2024-05-20 14:00:00.000
  

Since you didn't specify the exact target format, I'll cover the two most common functions for formatting dates in SQL Server, with examples of popular formats:

• 使用CONVERT函数（高性能，兼容所有版本）
  CONVERT uses predefined style codes to format datetime values. It's faster than the alternative, especially for large datasets:

  DECLARE @OriginalDate DATETIME = '2024-05-20 14:35:42';
  
  -- 转换为 'yyyy-MM-dd HH:mm:ss' 格式
  SELECT CONVERT(VARCHAR(19), @OriginalDate, 120) AS FormattedDate;
  -- 输出：2024-05-20 14:35:42
  
  -- 转换为 'MM/dd/yyyy' 格式
  SELECT CONVERT(VARCHAR(10), @OriginalDate, 101) AS FormattedDate;
  -- 输出：05/20/2024
  
  -- 转换为 'dd-MM-yyyy' 格式
  SELECT CONVERT(VARCHAR(10), @OriginalDate, 105) AS FormattedDate;
  -- 输出：20-05-2024
  

  You can find the full list of style codes in SQL Server's official documentation for all possible format options.

• 使用FORMAT函数（灵活，SQL Server 2012+）
  FORMAT uses .NET format strings, which are more intuitive for custom formats. Note that it has slightly worse performance than CONVERT, so use it sparingly for large datasets:

  DECLARE @OriginalDate DATETIME = '2024-05-20 14:35:42';
  
  -- 转换为 'yyyy-MM-dd HH:mm:ss' 格式
  SELECT FORMAT(@OriginalDate, 'yyyy-MM-dd HH:mm:ss') AS FormattedDate;
  -- 输出：2024-05-20 14:35:42
  
  -- 转换为 'MMMM dd, yyyy'（带月份全称）
  SELECT FORMAT(@OriginalDate, 'MMMM dd, yyyy') AS FormattedDate;
  -- 输出：May 20, 2024
  
  -- 转换为带时区的格式（适用于DATETIMEOFFSET类型）
  DECLARE @OriginalOffset DATETIMEOFFSET = '2024-05-20 14:35:42 +08:00';
  SELECT FORMAT(@OriginalOffset, 'yyyy-MM-dd HH:mm:ss zzz') AS FormattedDate;
  -- 输出：2024-05-20 14:35:42 +08:00
  

内容的提问来源于stack exchange，提问作者Rafael