tbbeg end
08:23 17:35
07:45 18:20
06:33 12:45要求:
1. beg 分钟部分不满30分钟的为30,超过30则算为00,然后小时部分加1
end 分钟部分不满30的则舍去为00,超过30则算为302. 1条update解决。
08:23 17:35
07:45 18:20
06:33 12:45要求:
1. beg 分钟部分不满30分钟的为30,超过30则算为00,然后小时部分加1
end 分钟部分不满30的则舍去为00,超过30则算为302. 1条update解决。
beg end
08:30 17:30
08:00 18:00
07:00 12:30
set @beg='06:33'select case when cast(substring(@beg,4,2) as int)>30 then cast((cast(substring(@beg,1,2) as int)+1)as varchar(30))+':00' else
substring(@beg,1,3)+'30' end
select
[beg]=case when datename(MI,[beg])<30 then datename(hh,[beg])+':30'
else datename(hh,DATEADD(hh,1,[beg]))+':00' end,
[end]=case when datename(MI,[end])>30 then datename(hh,[end])+':30'
else datename(hh,[end])+':00' end
from @tb--Result
/*
beg end
--------------------------------- ---------------------------------
8:30 17:30
8:00 18:00
7:00 12:30
*/
declare @tb table
(
beg varchar(10),
[end] varchar(10)
)insert @tb select '08:23','17:35'
union all select '07:45','18:20'
union all select '06:33','12:45'select
beg = case when convert(int,right(beg,2)) >=30
then '0' + ltrim(convert(int,left(beg,2)) + 1) + ':00'
else left(beg,2) + ':30'
end,
[end] = case when convert(int,right([end],2)) >= 30
then left([end],2) + ':30'
else left([end],2) + ':00'
end
from @tb
ELSE CAST(DATEPART(HH,DATEADD(HH,1,BEG)) AS VARCHAR(2) )+':00' END ),
[END]=(CASE WHEN DATEPART(MI,[END])>=30 THEN CAST(DATEPART(HH,[END]) AS VARCHAR(2) )+':30'
ELSE CAST(DATEPART(HH,[END]) AS VARCHAR(2) )+':00' END )
FROM A