SQL Server query / function to round to half

Question

I need a function that will always round to the nearest whole or half hour in SQL Server.

Ref.

1.2 = 1.5
1.5 = 1.5
1.6 = 2.0
1.0 = 1.0
0.2 = 0.5
0.8 = 1.0

I have found many options on how to solve ALMOST this, but I would like one effective for this purpose.

Thanks..

Answer 1

If you are dealing with numbers, the easiest way is to multiply by 2, take the ceiling (round to the nearest integer), and then divide by 2.

Select Ceiling(1.2 * 2) / 2
Select Ceiling(1.6 * 2) / 2

Since your question is referred to as “whole or half an hour”, here is some code that takes into account DateTime data:

Declare @Temp Table(Data DateTime)

Insert Into @Temp Values('20131114 11:00')
Insert Into @Temp Values('20131114 11:15')
Insert Into @Temp Values('20131114 11:30')
Insert Into @Temp Values('20131114 11:45')
Insert Into @Temp Values('20131114 11:59')

Select Data, DateAdd(Minute, Ceiling(DateDiff(Minute, '0:00', Data) / 30.0) * 30, '0:00')
From   @Temp