Converting minutes to HH:MM - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting minutes to HH:MM

Author

Topic

NEVerton
Starting Member

9 Posts

Posted - 2011-09-13 : 18:15:49

I need to convert a number of minutes into HH:MM format, e.g. 360 = 06:00, 15 = 00:15, 1440 = 24:00, 2160 = 36:00

Heeeelp! I'm going insane after numerous attempts to achieve this!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-09-13 : 18:24:48

I'm sure there's a better way to do this, but this seems to work:

declare @i int

set @i = 2160

select RIGHT('0' + CONVERT(varchar(5), @i/60), 2) + ':' + RIGHT('0' + CONVERT(varchar(5), CASE WHEN 60%@i = 60 THEN 0 WHEN 60%@i = 0 THEN @i ELSE 60%@i END), 2)

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 20:07:23

You could also do this, though it has the leading 00: before the minutes part:

[CODE]
CONVERT(CHAR(8), DATEADD(MINUTE, (@i/ 60.0), 0), 8)
[/CODE]

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 20:10:05

nix that, doesn't work for less than 1 minute. Back to the drawing board!

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2011-09-13 : 20:17:40

---Forgot to paste the declare/set on the first post. Kinda important :p
[CODE]
declare @i int
,@H int
,@M int
,@S int

select @i = 30
select @H=@i/3600
select @M=(@i % 3600) / 60
select @S=(@i % 3600) % 60

SELECT CONVERT(time, convert(char(2), @h)+':'+convert(char(2),@m)+':'+convert(char(2), @s),8)
[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 00:16:09

[code]
DECLARE @minute int
SET @minute=534

SELECT CONVERT(varchar(5),DATEADD(minute,@minute,0),108)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NEVerton
Starting Member

9 Posts

Posted - 2011-09-14 : 02:20:25

Sorry all, forgot to mention that the minutes returned could also be negative, i.e. -15 = -00:15, -360 = -06:00

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 03:35:24

DECLARE @minute int
SET @minute=534


SELECT CASE WHEN SIGN(@minute) = -1.0 THEN '-' ELSE '' END + CONVERT(varchar(5),DATEADD(minute,ABS(@minute),0),108)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

NEVerton
Starting Member

9 Posts

Posted - 2011-09-14 : 08:41:54

Unfortunately, this doesn't work for minutes equivalent to 1 day or more =, i.e. > 1440

SELECT CASE WHEN SIGN(@minute) = -1.0 THEN '-' ELSE '' END + CONVERT(varchar(5),DATEADD(minute,ABS(@minute),0),108)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-14 : 08:50:51

[code]SELECT CASE WHEN SIGN(@minute) = -1.0 THEN '-' ELSE '' END + CONVERT(varchar(10),ABS(@minute)/60) + ':' + CONVERT(varchar(10),ABS(@minute)%60)[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources