Rounding a datetime value down to the nearest half hour

Question

I have a requirement to round a datetime2 value down to the nearest half hour. For example '10/17/2013 12:10:00.123' would round down to '10/17/2013 12:00:00.0' And '10/17/2013 12:34:17.123' would round down to 10/17/2013 12:30:00.0'. My first thought was to create a UDF which would break the date and time apart and do it that way. However, I'm wondering if something like this can be done in a single T-SQL statement?

I'm using SQL Server 2012 and the data type of the column is a dateTime2 (which cannot be converted to a float!!)

Answer 1

Here is one way to do it:

update t set
  d = dateadd(minute,datediff(minute,'19000101',d)/30*30,'19000101');

Answer 2

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]

Answer 3

How about this

declare @d datetime = '2013-05-06 12:29.123'
select 
case 
    when datepart(minute, @d) < 30 then cast(dateadd(minute, -datepart(minute,@d)-datepart(second,@d), @d) as smalldatetime)
    when datepart(minute, @d) >= 30 then cast(dateadd(minute, -datepart(minute,@d)-datepart(second,@d)+30, @d) as smalldatetime)
end