How Can I Round Time Intervals to the Nearest 15 Minutes in T-SQL?

Round time intervals to the nearest 15 minutes

Determining the nearest 15-minute interval is a common task when working with time-related data. T-SQL provides an efficient way to do this without complex custom functions or case statements.

To round HH:MM values ​​to the nearest 15-minute interval, you can use the following formula:

SELECT dateadd(minute, datediff(minute, 0, GETDATE()) / 15 * 15, 0)

In this formula, GETDATE() represents the current datetime value. You can replace it with any valid datetime expression. The DATEDIFF function calculates the difference between the current date-time and the zero (0) date, representing the number of minutes since midnight. This value is then divided by 15 and rounded to the nearest whole number to determine the number of 15-minute intervals. Finally, DATEADD adds this number of minutes to the zero date to produce a rounded date-time.

Example:

SELECT dateadd(minute, datediff(minute, 0, '00:08:00') / 15 * 15, 0)

Output: 00:15:00

Using the above formula, you can effectively round any HH:MM value to the nearest 15-minute interval, regardless of time of day or year. This approach provides an elegant and efficient solution to this common time rounding task.

The above is the detailed content of How Can I Round Time Intervals to the Nearest 15 Minutes in T-SQL?. For more information, please follow other related articles on the PHP Chinese website!