CODEWITHSUNDEEP
sql-server
user3920526
user3920526

Reputation: 397

Convert Int to Time with int value having hour and minute

My values(int)     Expected
1107                11:07
1407                14:07
1202                00:02

My Sql Query 

Declare @t int
SET @t=1102
Select LTrim(Stuff(Right(Convert(varchar(26), DateAdd(ss, '' + 60*((@t % 10000)/100) + @t%100, '19000101'), 109), 14), 9, 6, ' '))

what i get

12:11:02

What i am expecting

11:02

If there is any easier way ? Thank you for having a look.

Upvotes: 1

Views: 324

Answers (1)

dognose
dognose

Reputation: 20899

Edit: Oh, you are on MSSql - leaving this here, if MySQL-Users come along in the future :)

The Idea should apply for MsSQL as well.

You should change your int-columns content, so it contains the second of the day i.e.

0      for 00:00:00
21.600 for 06:00:00
86.399 for 23:59:59

MySQL: Then you could just use

TIME_FORMAT(SEC_TO_TIME(seconds),'%H:%i')

and perform any maths required way easier.

http://sqlfiddle.com/#!9/9eecb/14236

MsSQL: Then you could just use

 FORMAT(DATEADD(ss,seconds,0), 'HH:mm')

and perform any maths required way easier.

http://sqlfiddle.com/#!18/9eecb/2639

As your int-columns cant be changed - I would stick with the idea of using seconds of the day - and you can convert your representation, by multiplying the first 2 digits with 3600 and the last 2 digits with 60:

Declare @t int
SET @t=903
SELECT FORMAT(DATEADD(ss,
 LEFT(@t,LEN(@t)-2) * 3600 + 
 RIGHT(@t,2) * 60 
,0), 'HH:mm')

http://sqlfiddle.com/#!18/9eecb/2654

edit: Something simpliefied would work as well, as there is actually no conversion required (This just wouldn't return a datetime type, but a string type): 

Declare @t int
SET @t=903
SELECT LEFT(@t,LEN(@t)-2) + ':' + RIGHT(@t,2)

But note the missing leading 0, when using this approach.

http://sqlfiddle.com/#!18/9eecb/2653

Upvotes: 1

Related Questions