How to Round T-SQL DateTime Values to the Nearest Minute or Hour?

T-SQL datetime value rounding method accurate to minutes or hours

SQL Server provides built-in functions to easily round datetime values ​​to the nearest minute or hour. For example, for the given datetime value '2007-09-22 15:07:38.850', you can use the following function:

<code class="language-sql">-- 舍入到最近的分钟
SELECT DATEADD(mi, DATEDIFF(mi, 0, @dt), 0)

-- 舍入到最近的小时
SELECT DATEADD(hour, DATEDIFF(hour, 0, @dt), 0)</code>

These methods truncate seconds and minutes respectively to achieve the desired rounding effect. If you need to round up or down, you can add or subtract half a minute or half an hour respectively before truncating:

<code class="language-sql">-- 舍入到最近的分钟（向上）
SELECT DATEADD(mi, DATEDIFF(mi, 0, DATEADD(s, 30, @dt)), 0)

-- 舍入到最近的小时（向上）
SELECT DATEADD(hour, DATEDIFF(hour, 0, DATEADD(mi, 30, @dt)), 0)</code>

Before the introduction of the date data type in SQL Server 2008, the above method could be used to truncate the time portion from the datetime type, leaving only the date portion. This works by calculating the number of days between the datetime and a fixed point in time (0, representing 1900-01-01 00:00:00.000), and then adding that number to the fixed point in time:

<code class="language-sql">-- 截断时间部分以仅获取日期
SELECT DATEADD(day, DATEDIFF(day, 0, @dt), 0)</code>

The above is the detailed content of How to Round T-SQL DateTime Values to the Nearest Minute or Hour?. For more information, please follow other related articles on the PHP Chinese website!