select getdate(), case when datepart(mi,getdate())<30 then right('00'+cast(datepart(hh,getdate())*2 as varchar(2)),2)+':'+right('00'+cast(datepart(mi,getdate()) as varchar(2)),2)+':'+right('00'+cast(datepart(ss,getdate()) as varchar(2)),2) when datepart(mi,getdate())>=30 then right('00'+cast(datepart(hh,getdate())*2+1 as varchar(2)),2)+':'+right('00'+cast(datepart(mi,getdate())-30 as varchar(2)),2)+':'+right('00'+cast(datepart(ss,getdate()) as varchar(2)),2) end
case
when datepart(mi,getdate())<30
then right('00'+cast(datepart(hh,getdate())*2 as varchar(2)),2)+':'+right('00'+cast(datepart(mi,getdate()) as varchar(2)),2)+':'+right('00'+cast(datepart(ss,getdate()) as varchar(2)),2)
when datepart(mi,getdate())>=30
then right('00'+cast(datepart(hh,getdate())*2+1 as varchar(2)),2)+':'+right('00'+cast(datepart(mi,getdate())-30 as varchar(2)),2)+':'+right('00'+cast(datepart(ss,getdate()) as varchar(2)),2)
end
convert(varchar(5),dateadd(n,number*30,0),108) as begintime,
convert(varchar(5),dateadd(n,number*30+30,0),108) as endtime
from master..spt_values
where type = 'P' and number < 24*60/30/*
begintime endtime
--------- -------
00:00 00:30
00:30 01:00
01:00 01:30
01:30 02:00
02:00 02:30
02:30 03:00
03:00 03:30
03:30 04:00
04:00 04:30
04:30 05:00
05:00 05:30
05:30 06:00
06:00 06:30
06:30 07:00
07:00 07:30
07:30 08:00
08:00 08:30
08:30 09:00
09:00 09:30
09:30 10:00
10:00 10:30
10:30 11:00
11:00 11:30
11:30 12:00
12:00 12:30
12:30 13:00
13:00 13:30
13:30 14:00
14:00 14:30
14:30 15:00
15:00 15:30
15:30 16:00
16:00 16:30
16:30 17:00
17:00 17:30
17:30 18:00
18:00 18:30
18:30 19:00
19:00 19:30
19:30 20:00
20:00 20:30
20:30 21:00
21:00 21:30
21:30 22:00
22:00 22:30
22:30 23:00
23:00 23:30
23:30 00:00(48 行受影响)
*/
和你的问题有点像
http://topic.csdn.net/u/20100621/22/b1a24c13-9ef7-40cd-9e8b-5e4fb5d5a083.html