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
MTNCOMP | List | Table

myBlog

myBlog Home

Blog


SQL

SQL programming legacy app fixed time field quick fix

by Mountain Computers Inc., Publication Date: Saturday, July 27, 2019
View Count: 349, Keywords: SQL, Fixed Time field, Substring, Convert, Legacy App, Hashtags: #SQL #FixedTimefield #Substring #Convert #LegacyApp



 
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.

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