将日期时间值向下舍入到最接近的半小时 [英] Rounding a datetime value down to the nearest half hour

问题描述

我需要将 datetime2 值四舍五入到最接近的半小时.例如 '10/17/2013 12:10:00.123' 将向下舍入为 '10/17/2013 12:00:00.0' 而 '10/17/2013 12:34:17.123' 将向下舍入为 10/17/2013 12:30:00.0'.我的第一个想法是创建一个 UDF，它将日期和时间分开并这样做.但是，我想知道这样的事情是否可以在单个 T-SQL 语句中完成?

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?

我使用的是 SQL Server 2012，列的数据类型是 dateTime2(不能转换为浮点数！！)

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

推荐答案

Ian 的回答很好，但它包含了不必要的转换.我建议

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]

这篇关于将日期时间值向下舍入到最接近的半小时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！