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

Behind the Functions

The prime characters of our primary solution are the DATEADD and DATEDIFF functions. We're using ''20000101'' as our base time, considering it as a fixed point in time. This technique offers stellar consistency for our datetime rounding calculations.

When planning for rounding to the nearest minute or hour, adding or subtracting 30 seconds (half a minute) or 1800 seconds (half an hour) is a step forward. Then, rolling out the ROUND function will ensure that the timestamp is chiseled carefully to the closest minute or hour.

Diving into Details

Casting Out: When to use CAST and CONVERT

In terms of rounding to the nearest minute or hour, CAST and CONVERT offer interesting alternatives:

When the mission is to round down to the nearest minute:

-- Not trying to convert you to my beliefs, but this works 😉
SELECT CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 120))


For a much simpler output, like HH:MM:

-- Guess we're converting to a 1080p('108') time format! 
SELECT CONVERT(CHAR(5), GETDATE(), 108)


In these examples, the seconds are trimmed off, delivering a clean, minute-rounded result.

Azure's Turbo Functions: DATETRUNC and DATE_BUCKET

If you're utilizing SQL Server 2022/Azure, the nifty functions DATETRUNC and DATE_BUCKET can ease datetime rounding:

Get the nearest minute with DATETRUNC:

-- I heard you like truncating... so I brought a bucket
SELECT DATETRUNC('minute', GETDATE())


Achieve the nearest minute using DATE_BUCKET:

-- Fill your buckets! It's time... No, literally - it's time
SELECT DATE_BUCKET(1, MINUTE, GETDATE())


Variable inputs

When dealing with dynamic data, you may consider encapsulating the rounding logic into a user-defined function or stored procedure. This improves code reuse and simplifies maintenance.

Hurdles to Hop

Watch out for time zones

Be mindful of time zones, as an error there could lead to inaccuracies in your rounded times.

Precision Precautions

Remember, ROUND as a function has its limitations when it comes to precision. For very large date ranges, tiny differences might not be accurately represented.

Version vulnerabilities

When working with different versions of SQL Server, always factor in version compatibility. Some functions might not be available or behave differently in various versions.