My table had a column of type varchar. It contained Hours in the format 40:20 where 40 is the hours and 20 is the minutes. I wanted to convert it into minutes of type int. 40:20 should be converted to 2420.
I used the following strategy:
1) Split the varchar using ":" delimiter.
select * from dbo.split('40:20',':')
This will return two rows. Row one contains 40 and row two contains 20.
2) Now multiply the first row with 60 to convert 40 hours into minutes and then add it with the value in the second row. i.e., (40*60)+20
The complete SQL query is as follows. It also includes the split function call.
WITH items as(
select function_call.items, row_number() over (order by function_call.dummy) rownumber
from (
select items, 'dummy' dummy from dbo.split('40:20',':')) as function_call
)
select distinct ((cast((select items from items where rownumber = 1) as int)* 60) + cast((select items from items where rownumber = 2) as int)) minutes from items
The output of the above query execution is as follows:
I used the following split function, reused from another site:
CREATE FUNCTION dbo.Split(@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (items varchar(8000))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx!= 0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Items) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end
return
end
The split function can be independantly used as follows:
select * from dbo.split('40:20',':')
