How to Efficiently Round T-SQL Time Values to the Nearest 15-Minute Interval?

Exact calculation: Round T-SQL time values ​​to the nearest 15 minute interval

When dealing with time-based data in T-SQL, it is often necessary to round values ​​to a specific time interval. A common requirement is to round HH:MM values ​​to the nearest 15 minute interval.

Efficient rounding method

An efficient way to round time values ​​to the nearest 15-minute interval without relying on complex UDF or CASE statements, instead utilizing the DATEADD and DATEDIFF functions. This method includes:

• Use DATEDIFF to calculate the difference between a given time value and the zero (0) date.
• Use integer division to divide the difference by 15 to determine how many 15-minute intervals have elapsed.
• Use DATEADD to multiply the result by 15 and add the appropriate number of minutes to the zero date.

Example

Apply this method to your specific need for calculations using the CONVERT, DATEADD and DATEDIFF functions and you will get the following query:

SELECT DATEADD(MINUTE, ROUND(DATEDIFF(MINUTE, 0, CONVERT(CHAR(8), DATEADD(n, SUM(DATEDIFF(n, starttime, stoptime)), 0), 108)) / 15.0, 0) * 15, 0)

This calculation rounds the HH:MM time value exactly to the nearest 15 minute interval, ensuring that a value like 00:08:00 becomes 00:15:00 and 00:07:00 becomes 00: 00:00.

Other methods

While the DATEADD and DATEDIFF methods are efficient and accurate, there are several other methods you can consider for rounding time values ​​in T-SQL:

• Use the ROUND function and specify the interval:

    ROUND(time_value, 15)

• Using SQL Server 2012’s FLOOR function:

    FLOOR(DATEADD(MINUTE, time_value, 0))

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