--> 测试数据:#tb IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb GO CREATE TABLE #tb([上班时间] DATETIME) INSERT #tb SELECT '2014/04/04 12:43' UNION ALL SELECT '2014/04/05 06:46' UNION ALL SELECT '2014/04/07 12:21' --------------开始查询--------------------------SELECT DATEADD(mi,DATEDIFF(mi,1,[上班时间])/30*30,1) FROM #tb ----------------结果---------------------------- /* (No column name) 2014-04-04 12:30:00.000 2014-04-05 06:30:00.000 2014-04-07 12:00:00.000 */
SELECT CASE WHEN substring('2014/04/04 12:43',len('2014/04/04 12:43')-1,2)<30 THEN '你自己写' ELSE '你自己写' end 要么你就用楼上的把,我要去吃饭了,简单写了下,自己在套下就可以
IF OBJECT_ID('tempdb.dbo.#tb') IS NOT NULL DROP TABLE #tb
GO
CREATE TABLE #tb([上班时间] DATETIME)
INSERT #tb
SELECT '2014/04/04 12:43' UNION ALL
SELECT '2014/04/05 06:46' UNION ALL
SELECT '2014/04/07 12:21'
--------------开始查询--------------------------SELECT DATEADD(mi,DATEDIFF(mi,1,[上班时间])/30*30,1) FROM #tb
----------------结果----------------------------
/*
(No column name)
2014-04-04 12:30:00.000
2014-04-05 06:30:00.000
2014-04-07 12:00:00.000
*/
要么你就用楼上的把,我要去吃饭了,简单写了下,自己在套下就可以