SQL programming legacy app fixed time field quick fix

 

always fun to solve a quick problem for a developer friend / client. he was trying to fix a legacy database app that had fixed time fields... as you can see, he just wanted the difference in times.

 

create database t

create table t (
user_id int,
dayofweek varchar(15),
timerange varchar(15)
)

insert into t (user_id, dayofweek, timerange) select 83, 'monday', '00:00/12:30'
insert into t (user_id, dayofweek, timerange) select 83, 'tuesday', null
insert into t (user_id, dayofweek, timerange) select 83, 'wednesday', '00:00/24:00'
insert into t (user_id, dayofweek, timerange) select 83, 'thursday', '03:01/10:02'
insert into t (user_id, dayofweek, timerange) select 83, 'friday', '00:00/24:00'
insert into t (user_id, dayofweek, timerange) select 83, 'saturday', '00:00/24:00'
insert into t (user_id, dayofweek, timerange) select 83, 'sunday', '00:00/24:00'

select *,
substring(timerange, 1, 2) as [timerange1hours],
substring(timerange, 4, 2) as [timerange1minutes],
substring(timerange, 7, 2) as [timerange2hours],
substring(timerange, 10, 2) as [timerange2minutes],
convert(int, substring(timerange, 7, 2)) - convert(int, substring(timerange, 1, 2)) as [diffhours],
convert(int, substring(timerange, 10, 2)) - convert(int, substring(timerange, 4, 2)) as [diffminutes]
from t
where timerange is not null

delete  from t

 

drop database t