-- SQL时间取整的问题
CREATE TABLE TEST(Startdate datetime)
insert test VALUES (getdate())
go
-- 取整,小时不加1
SELECT StartDate,
CAST
(
CONVERT(VARCHAR(10),StartDate,120) + ' ' +
CONVERT(VARCHAR,DATEPART(hh, StartDate))+ ':00:00'
AS datetime
) AS [取整(舍掉分秒)]
FROM TEST
go
-- 取整,小时数加1
SELECT startdate,
dateadd(
hour,
CASE WHEN DATEPART(minute, startdate)> 0 OR
DATEPART(second, startdate) > 0 OR
DATEPART(millisecond, startdate) > 0
THEN 1 ELSE 0 END,
CAST(
CONVERT(VARCHAR(10),StartDate,120) + ' ' +
CONVERT(VARCHAR,DATEPART(hh, StartDate))+ ':00:00'
AS datetime)
) as [取整(舍掉分秒,小时加1)]
FROM TEST
go