sql - SQL Server:向上日期

我想把日期时间值四舍五入到最近的一整天。

UPDATE [DB].[dbo].[TABLE]
-- I'm unsure how to take the DepartureDate and round it up
SET DepartureDate = DepartureDate + ??
WHERE DATEPART(HOUR, DepartureDate) = 23

DepartureDate是一个datetime列。
所以2017-11-10 23:00:00.0000变成2017:11:11 00:00:00.0000

最佳答案

试试这个：

SELECT DepartureDate,
       CASE WHEN DepartureDate = CONVERT(DATE, DepartureDate)
            THEN DepartureDate
            ELSE CONVERT(DATE,DepartureDate + 1)
       END RoundedDate
FROM YourTable;

这将把DepartureDate汇总到第二天，除非DepartureDate正好在午夜。它利用了date类型的默认时间为午夜这一事实。
更新内容如下：

UPDATE YourTable
SET DepartureDate = CASE WHEN DepartureDate = CONVERT(DATE, DepartureDate)
                         THEN DepartureDate
                         ELSE CONVERT(DATE,DepartureDate + 1)
                    END;

编辑，因为在注释中澄清了您只想在hour=23时执行此操作，然后执行此操作：

UPDATE YourTable
SET DepartureDate = CONVERT(DATE,DepartureDate +1)
WHERE DATEPART(HOUR, DepartureDate) = 23