How to Round T-SQL DateTime to the Nearest Minute and Hour?

Use function to round T-SQL DateTime to nearest minute and hour

Question:

How to round a datetime column to the nearest minute and hour using existing functions?

Solution:

To round a datetime column to the nearest minute, use the following code:

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

Where @dt is the datetime column you want to round.

To round a datetime column to the nearest hour, use the following code:

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

For example, if your @dt column value is '09-22-2007 15:07:38.850', these queries will return:

• Last minute: 2007-09-22 15:07:00.000
• Last hour: 2007-09-22 15:00:00.000

Round up or down:

To round up or down, add half a minute or half an hour respectively, then truncate:

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

These queries will return:

• Nearest minute, rounded up: 2007-09-22 15:08:00.000
• Nearest hour, rounded up: 2007-09-22 16:00:00.000

Truncate the time part to get the date:

Before SQL Server 2008 introduced the date data type, you could use a similar method to truncate the time portion from a datetime to get just the date:

<code class="language-sql">-- 获取 datetime 和固定点 (1900-01-01 00:00:00.000) 之间的日数
declare @days int
set @days = datediff(day, 0, @dt)

-- 将日数添加到固定点以获取日期，并将时间设置为 00:00:00.000
select dateadd(day, @days, 0)</code>

This will return the date portion of the @dt column with the time set to 00:00:00.000.

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