GoGreen PC TuneUp™
Learn More

Insta-Install™
this is how we code sign our software and ssl encrypt our websites from our affiliate partner SSL.com

myBlog

myBlog Home

Back to Blog MTNCOMP


SQL

SQL programming legacy app fixed time field quick fix

Published: Saturday, July 27, 2019 written by Andy Flagg
View Count: 128
Keywords: SQL, Fixed Time field, Substring, Convert, Legacy App



 
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



if you found this article helpful, consider contributing $10, 20 an Andrew Jackson or so..to the author. more authors coming soon
FYI we use paypal or patreon, patreon has 3x the transaction fees, so we don't, not yet.

© 2020 myBlog™ v1.1 All rights reserved. We count views as reads, so let's not over think it.