'065934' -> '070000'
'064558' -> '064600'
이런 형태의 varchar형의 시간이 있는데
초단위 30초가 넘으면 반올림 되서 분의 형태가 바뀌고 분이 59 지나면 시간이 변화되는,,,,
프로지저나 쿼리도 상관 없는데 ^^.
with test as ( select hhmmss , convert( integer,( round((left(hhmmss,2) * 3600 + substring(hhmmss ,3,2) * 60 + RIGHT(hhmmss,2)) / 60. ,0 ) * 60 )) time from ( select '065934' hhmmss union all select '064558'hhmmss union all select '235839' hhmmss union all select '235939' hhmmss ) as r ) select hhmmss , right('0'+convert( varchar(2) ,time / 3600 ),2) + right('0'+convert(varchar(2) ,(time % 3600) / 60 ),2) + right('0'+convert(varchar(2) ,(time % 3600) % 60),2) from test
create table pp
(va varchar(40))
insert into pp values('065934'),('064558')
select va
,ceiling(datediff(second, '1900-01-01', CONVERT(datetime, STUFF(STUFF(va,3,0,':'),6,0,':')))/30.0)*30
,DATEADD(second, ceiling(datediff(second, '1900-01-01', CONVERT(datetime, STUFF(STUFF(va,3,0,':'),6,0,':')))/30.0)*30,'1900-01-01')
,REPLACE(CONVERT(varchar, dateadd(second, ceiling(datediff(second, '1900-01-01', CONVERT(datetime, STUFF(STUFF(va,3,0,':'),6,0,':')))/30.0)*30,'1900-01-01'), 108),':','')
from pp with(nolock)
