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

Round datetime value to nearest minute or hour in T-SQL

T-SQL provides built-in functions to round datetime values ​​to the nearest minute or hour. Rounded values ​​are valuable for data analysis and report generation.

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>

Where, @dt represents the datetime value to be rounded.

For example, rounding the value '09-22-2007 15:07:38.850' to the nearest minute results in '2007-09-22 15:07:00.000'.

To round a datetime value to the nearest hour, use:

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

This formula effectively truncates seconds and minutes from a datetime value. Using the same example, rounding '09-22-2007 15:07:38.850' to the nearest hour results in '2007-09-22 15:00:00.000'.

Additionally, rounding up or down can be achieved by adding half a minute or half an hour to the datetime value before truncation.

Example:

<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>

The result is:

<code>2007-09-22 15:08:00.000
2007-09-22 15:00:00.000</code>

Before the introduction of the date data type in SQL Server 2008, a similar technique was used to truncate the hours, minutes and seconds portion of a datetime value to obtain only the date. The idea is to determine the number of days between a datetime and a fixed point in time (0, meaning 1900-01-01 00:00:00.000). By adding this number of days to a fixed point in time, you get the original date with the time set to 00:00:00.000.

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