Rounding time in SQL Server

Question

I need to round time to the nearest quarter in SQL Server.

For example:

• '2016-11-06 13:12:23.000' --> '2016-11-06 13:15:00.000'

• '2016-11-06 13:06:23.000' --> '2016-11-06 13:00:00.000'

I have tried this so far

declare @var datetime = '2016-11-06 13:12:35.000'

select @var as original_time

select 
    dateadd(minute, datediff(minute, 0, @var) / 15 * 15, 
    iif(datediff(minute, 0, @var) % 15.0 < 7.5, 0, 
    iif(datediff(minute, 0, @var) % 15.0 < 21.0, 15, 30 ) ))

Output:

The expected result is '2016-11-06 13:15:00.000' but this is the output I get.

I referred to this question before the attempt.

Answer 1

Convert to float give the date as a whole number and time as a fraction. Then round to 24 (hours) * 4 (quarters)

select CONVERT(DATETIME,ROUND(CONVERT(FLOAT, @var)*24*4,0)/24/4)

Change ROUND to CEILING or FLOOR for round up or down