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

T-SQL datetime rounding to minutes and hours

This article explains how to round datetime values ​​to the nearest minute or hour in T-SQL.

Round to the nearest minute and hour

To round a datetime value to the nearest minute, use the following formula:

<code class="language-sql">dateadd(mi, datediff(mi, 0, @dt), 0)</code>

Wherein, @dt represents the input datetime value.

Similarly, to round to the nearest hour, use:

<code class="language-sql">dateadd(hour, datediff(hour, 0, @dt), 0)</code>

These functions will truncate seconds or milliseconds from the resulting datetime value.

Example:

For the given input datetime value '2007-09-22 15:07:38.850', the rounded result is:

<code>2007-09-22 15:07:00.000 （舍入到最接近的分钟）
2007-09-22 15:00:00.000 （舍入到最接近的小时）</code>

Round up or down

If you want to round up or down, you can add half a minute or half an hour respectively before truncation:

<code class="language-sql">dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)</code>

In this case, the rounded result becomes:

<code>2007-09-22 15:08:00.000 （向上舍入到最接近的分钟）
2007-09-22 16:00:00.000 （向上舍入到最接近的小时）</code>

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