参考这个:-- 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 godrop table test go
首先,可以用datediff()取得秒数差,然后再与60相除及取模以得出最后的值。
cast(@a/60 as int)+1 cast((@a-0.001)/60 as int ) +1
declare @t table(秒 int) insert @t select 59 union all select 60 union all select 90 union all select 120 union all select 130 select 秒,分钟 = floor(1.0*秒/61) + 1 from @t/*结果 秒 分钟 ---------------------------- 59 1 60 1 90 2 120 2 130 3 */
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
godrop table test
go
cast((@a-0.001)/60 as int ) +1
insert @t
select 59 union all
select 60 union all
select 90 union all
select 120 union all
select 130 select 秒,分钟 = floor(1.0*秒/61) + 1 from @t/*结果
秒 分钟
----------------------------
59 1
60 1
90 2
120 2
130 3
*/