How to Round HH:MM Time Values to the Nearest 15-Minute Interval in T-SQL?

Round HH:MM time values ​​to the nearest 15 minute interval in T-SQL

In data analysis, rounding time values ​​to the nearest specified interval is a common task. In this example, our goal is to round the HH:MM values ​​to the nearest 15-minute interval.

Methods that do not require user-defined functions

While using user-defined functions (UDFs) or CASE statements is a viable approach, there are more elegant ways to avoid using them. One way is to utilize the DATEADD and DATEDIFF functions:

<code class="language-sql">select dateadd(minute, datediff(minute,0,GETDATE()) / 15 * 15, 0)</code>

Here, GETDATE() represents the current date and time value. The DATEDIFF function calculates the difference in minutes between the current time and a fixed reference date of 0. We then divided this difference by 15 and multiplied by 15 to get the nearest 15 minute interval. Finally, the DATEADD function adds this interval to the reference date, resulting in a rounded time value.

Notes

This method is suitable for cases where only minutes are relevant and seconds can be ignored. Due to potential overflow issues, it can handle dates up to the year 5500. However, using a specific date (for example, "2009-01-01") or today's date as a reference point can work around this limitation.

Example

For example, using the method above to round a timestamp like "00:08:00" to the nearest 15-minute interval would result in "00:15:00". Similarly, "00:07:00" will be rounded to "00:00:00".

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