T-SQL Datetime Rounded to Nearest Minute and Nearest Hours With Using Functions

T-SQL datetime rounded to nearest minute and nearest hours with using functions

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

will return

2007-09-22 15:07:00.000
2007-09-22 15:00:00.000

The above just truncates the seconds and minutes, producing the results asked for in the question. As @OMG Ponies pointed out, if you want to round up/down, then you can add half a minute or half an hour respectively, then truncate:

select dateadd(mi, datediff(mi, 0, dateadd(s, 30, @dt)), 0)
select dateadd(hour, datediff(hour, 0, dateadd(mi, 30, @dt)), 0)

and you'll get:

2007-09-22 15:08:00.000
2007-09-22 15:00:00.000

Before the date data type was added in SQL Server 2008, I would use the above method to truncate the time portion from a datetime to get only the date. The idea is to determine the number of days between the datetime in question and a fixed point in time (0, which implicitly casts to 1900-01-01 00:00:00.000):

declare @days int
set @days = datediff(day, 0, @dt)

and then add that number of days to the fixed point in time, which gives you the original date with the time set to 00:00:00.000:

select dateadd(day, @days, 0)

or more succinctly:

select dateadd(day, datediff(day, 0, @dt), 0)

Using a different datepart (e.g. hour, mi) will work accordingly.

T-SQL: Round to nearest 15 minute interval

This was answered here How to Round a Time in T-SQL and i think it should work for you to.

CREATE FUNCTION [dbo].[RoundTime] (@Time datetime, @RoundTo float) RETURNS datetime
AS
BEGIN
    DECLARE @RoundedTime smalldatetime, @Multiplier float

    SET @Multiplier = 24.0 / @RoundTo

    SET @RoundedTime= ROUND(CAST(CAST(CONVERT(varchar, @Time, 121) AS datetime) AS float) * @Multiplier, 0) / @Multiplier

    RETURN @RoundedTime
END

-- Usage    
SELECT dbo.RoundTime('13:15', 0.5)

SQL Server - Round TIME values to the next minute

SELECT  DATEADD(MINUTE, CEILING(DATEDIFF(SECOND, 0, CAST(CAST(PA.ORA_INIZIO AS DATETIME) AS TIME)) / 60.0), DATEDIFF(DAY, 0, PA.ORA_INIZIO)) AS BEGIN_TIME_ROUNDED

EDIT

As pointed out in a comment this fails for times between 0 and 1 second. This can be combatted by simply changing the precision in the ceiling from seconds to milliseconds:

SELECT  PA.ORA_INIZIO,
        DATEADD(MINUTE, 
            CEILING(DATEDIFF(MILLISECOND, 0, CAST(PA.ORA_INIZIO AS TIME)) / 60000.0),
            DATEDIFF(DAY, 0, PA.ORA_INIZIO)) AS BEGIN_TIME_ROUNDED
FROM (VALUES 
        (CONVERT(DATETIME, '20211126 15:59:00.997')), 
        (CONVERT(DATETIME, '20211126 15:59:00.004'))
    ) AS PA (ORA_INIZIO);

Which gives:

ORA_INIZIO	BEGIN_TIME_ROUNDED
2021-11-26 15:59:59.997	2021-11-26 16:00:00.000
2021-11-26 15:59:00.003	2021-11-26 16:00:00.000

How do i get the full hour instead of the minutes?

Use date maths and a "magic" date:

DATEADD(HOUR,DATEDIFF(HOUR,0,YourColumn),0);

This gets the number of hours between the "date" 0 (1900-01-01) and your date value, and then adds that many hours to the "date" 0.

On SQL Server 2022 (currently in preview), however, you have access to DATETRUNC and DATE_BUCKET that make this much easier:

DATETRUNC(HOUR,YourColumn),
DATE_BUCKET(HOUR,0,YourColumn)

Round time to 5 minute nearest SQL Server

Create this function

create function f_round5min
(
@date datetime
) returns datetime
as
begin -- adding 150 seconds to round off instead of truncating
return dateadd(minute, datediff(minute, '1900-01-01', dateadd(second, 150, @date))/5*5, 0)
end

Use this syntax:

declare @testtable table(date datetime)

insert @testtable values('2013-11-12 12:00'),('2013-11-12 12:01'),
('2013-11-12 12:02'),('2013-11-12 12:02:29'),('2013-11-12 12:02:30'),
('2013-11-12 12:02:31'),('2013-11-12 12:03'),('2013-11-12 12:04'),
('2013-11-12 12:05')

select date, dbo.f_round5min(date) rounded from @testtable

Result:

date                     rounded
2013-11-12 12:00:00.000  2013-11-12 12:00:00.000
2013-11-12 12:01:00.000  2013-11-12 12:00:00.000
2013-11-12 12:02:00.000  2013-11-12 12:00:00.000
2013-11-12 12:02:29.000  2013-11-12 12:00:00.000
2013-11-12 12:02:30.000  2013-11-12 12:05:00.000
2013-11-12 12:02:31.000  2013-11-12 12:05:00.000
2013-11-12 12:03:00.000  2013-11-12 12:05:00.000
2013-11-12 12:04:00.000  2013-11-12 12:05:00.000
2013-11-12 12:05:00.000  2013-11-12 12:05:00.000

Round SQL Time to nearest hour if 1min away from nearest hour

You can make a decision if you try to add one minute and then test the result:

SELECT 
    cast(dateadd(millisecond, 29999, @t) as smalldatetime) as T1, 
    cast(dateadd(millisecond, 29999, @t2) as smalldatetime) as T2
    ,CASE WHEN SUBSTRING(CAST(DATEADD(MINUTE, 1, @t) as nvarchar(15)),4,2) = '00' THEN DATEADD(MINUTE, 1, @t) ELSE @t END
    ,CASE WHEN SUBSTRING(CAST(DATEADD(MINUTE, 1, @t2) as nvarchar(15)),4,2) = '00' THEN DATEADD(MINUTE, 1, @t2) ELSE @t2 END

SQL Netezza - How to round datetime to nearest hour

You can use date_trunc(). If by "round" you mean truncate, then there is a built-in function:

date_trunc('hour', col)

If by "round" you really mean "round", then:

date_trunc('hour', col + interval '30 minute')

Round to nearest MINUTE or HOUR in Standard SQL BigQuery

Below is for BigQuery Standard SQL

#standardSQL
WITH `project.dataset.table` AS (
  SELECT DATETIME '2018-01-01 01:05:56' input_datetime 
)
SELECT input_datetime,
  DATETIME_TRUNC(input_datetime, MINUTE) rounded_to_minute,
  DATETIME_TRUNC(input_datetime, HOUR) rounded_to_hour
FROM `project.dataset.table`

with result as

Row     input_datetime          rounded_to_minute       rounded_to_hour  
1       2018-01-01T01:05:56     2018-01-01T01:05:00     2018-01-01T01:00:00

For TIMESTAMP or TIME data types - you can use respectively - TIMESTAMP_TRUNC() or TIME_TRUNC()

Rounding a datetime value down to the nearest half hour

The answer by Ian is good, but it contains an unnecessary conversion. I suggest

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0,1)/48.0) FROM [tableName]

If you want to round to the nearest half-hour instead of always rounding down, use

SELECT CONVERT(smalldatetime, ROUND(CAST([columnname] AS float) * 48.0,0)/48.0) FROM [tableName]

T-SQL Datetime Rounded to Nearest Minute and Nearest Hours With Using Functions