# Thread: Help with calculations of hour day min .

1. Registered User
Join Date
Aug 2009
Posts
262

## Unanswered: Help with calculations of hour day min .

Goodday,

I am unable to get around this issue.

For any given number of min, it is needed to convert into years, days, hours and minutes.

for e.g 120min = 2 hours 0 min

155 min = 2 hours 35min .

1450 = 1 day 0 hour 10min .

how can i get this value with sql query .

2ndly in form of a SP ,

when it is 120min given . the return value should be as 2 days, instead of 2days zero min ( since the min =0 )

i have been working for serverel hours now and none on my queries have gone ahead from basic . i admit i am a total failure at this atm .

any help would be appriciated .

mishaal

version sqlserver 2005 .

minutes.
_______
60
90
120
150
180
55
550000
122
956565

2. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Code:
```declare	@Minutes int
set		@Minutes = 1823456789

select	(@Minutes % (60)) as Minutes,
(@Minutes % (60 * 24)) / 60 as Hours,
(@Minutes % (60 * 24 * 365)) / (60 * 24) as Days,
(@Minutes / (60 * 24 * 365)) as Years```

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
...or, as the string you want:
Code:
```select	coalesce(convert(varchar(10), nullif((@Minutes / 525600), 0)) + ' Years ', '')
+ coalesce(convert(varchar(10), nullif((@Minutes % 525600) / 1440, 0)) + ' Days ', '')
+ coalesce(convert(varchar(10), nullif((@Minutes % 1440) / 60, 0)) + ' Hours ', '')
+ coalesce(convert(varchar(10), nullif(@Minutes % 60, 0)) + ' Minutes', '') as ElapsedTime```

4. Registered User
Join Date
Mar 2009
Location
Australia
Posts
113
I'd rtrim() that string too - if you have no minutes you end up with a space on the end.

And then depending on how anal the end users are you might want to look at not pluralising years/days/hours/minutes when they are 1.

Join Date
Feb 2004
Location
New Zealand
Posts
1,482
And here is my version

Code:
```-- =============================================
-- Author: 	Stephan
-- Create date: 	12/02/09
-- Description:	This wilL Convert Min to Hours:mins
-- =============================================

-- Will use the ASP to drop everythng before the :

ALTER FUNCTION dbo.Mintohrs (@mins Int) RETURNS varchar(6)

AS
Begin
DECLARE @MYHH AS INT
DECLARE @TEMP AS INT
DECLARE @MYMIN AS INT

SET @MYHH = (@mins/60)
SET @TEMP = (@MYHH*60)
SET @MYMIN = (@mins-@TEMP)

Return CAST( @MYHH AS varchar(4)) +':'+ CAST( @MYMIN AS varchar(2))
End```
with a bit of thinking you could add the Days to it
by mod the MYHH would give you Days

I store all work in Mins then use this function when outputing to reports
Last edited by myle; 04-13-11 at 00:03.

6. Registered User
Join Date
Aug 2009
Posts
262
Thank you Blind man .. and thank you all .

so far i have worked this out .

Code:
```Select sno, m_time,
cast ((m_time / (60*24*365))%365 as varchar) + ' Year ' +
cast ((m_time / (60*24*31))%12 as varchar) + ' Month ' +

cast ((m_time / (60*24))%31 as varchar) + ' Days ' +
Cast((m_time / 60)%24 as Varchar) + ' Hours ' +
Cast(m_time % 60 as Varchar) + ' Minutes '
as [TotalHoursAndMinutes]
From
time_t```

Code:
```sno         TimeInMintue TotalHoursAndMinutes
----------- ------------ -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           60           0 Year 0 Month 0 Days 1 Hours 0 Minutes
2           90           0 Year 0 Month 0 Days 1 Hours 30 Minutes
3           120          0 Year 0 Month 0 Days 2 Hours 0 Minutes
4           150          0 Year 0 Month 0 Days 2 Hours 30 Minutes
5           180          0 Year 0 Month 0 Days 3 Hours 0 Minutes
6           55           0 Year 0 Month 0 Days 0 Hours 55 Minutes
7           550000       1 Year 0 Month 9 Days 22 Hours 40 Minutes
8           122          0 Year 0 Month 0 Days 2 Hours 2 Minutes
9           956565       1 Year 9 Month 13 Days 6 Hours 45 Minutes
10          525600       1 Year 11 Month 24 Days 0 Hours 0 Minutes
11          1450         0 Year 0 Month 1 Days 0 Hours 10 Minutes
12          7250         0 Year 0 Month 5 Days 0 Hours 50 Minutes
13          43200        0 Year 0 Month 30 Days 0 Hours 0 Minutes
14          518400       0 Year 11 Month 19 Days 0 Hours 0 Minutes

(14 row(s) affected)```

But the porblem i am facing is , 525600 is the amount of minutes in an year , but this is calculating 1 year , 11 months , 24 days ......
As far as i understand it is due to mod 30 ( % devident ? ) .

should i not use it ? . now i am trying the method u sir blindman have stated.

Is there any work around for this , i.e. some how telling the sp or query to add days as the number of months increase .
i.e. if it have 6 months .. so it add 3 days ( 1440*3 for min ) for Jan , march , may ,, or will it be bad ...

7. Registered User
Join Date
Aug 2009
Posts
262
Good god lord , is it that simple ? ..

declare @Minutes int
set @Minutes = 525600

select (@Minutes % (60)) as Minutes,
(@Minutes % (60 * 24)) / 60 as Hours,
(@Minutes % (60 * 24 * 365)) / (60 * 24) as Days,
(@Minutes % (60 * 24 * 365)) / (60 * 24) as [MONTH],

(@Minutes / (60 * 24 * 365)) as Years

How can i calculate the month here . I have admitted it to myown self that i am worse at calculations .

8. Registered User
Join Date
May 2005
Location
South Africa
Posts
1,369
It all depends on your epoch
Do you want to calculate number of months from now/1990/1970 onward/backward
60 days in 2008 is 2 months 0 days
60 days in 2009 is 2 months 1 day

9. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I make it look simple.
"Perfection is achieved not when there is no longer anything to add, but when there is no longer anything to take away". --Antoine de Saint Exupery

Calculating months is going to be impossible without an actual start date. And in that case, you could just use the datediff function to return the various parts.

10. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
use the datediff functions? Oh, come on. Think of all the questions he could post about how to handle non-31 day months. And then there is leap year, next year to think about.

11. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
Read my post. With a start date in addition to minutes, the datediff functions will account for different month lengths accurately.

12. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
And for that matter, the method I supplied above assumes standard years, not leap years.

13. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,862
Hmm....The joke tags must be broken ;-)

14. Registered User
Join Date
Aug 2009
Posts
262

## Thank you BlindMan

Thank you blindman. Through your simple but most effective approach i was able to make this. And i did follow u entirely .

Code:
```Create procedure  [dbo].[MinuteToString]
@Minutes INT

as
begin

select	coalesce(convert(varchar(10), nullif((@Minutes / 525600), 0)) + ' Year(s) ', '') +
CASE WHEN ((@Minutes % 525600) / 43800) <12
THEN coalesce(convert(varchar(10), nullif((@Minutes % 525600) / 43800, 0)) + ' Month(s) ', '')
ELSE ''
END +
CASE WHEN  ((@Minutes % 525600) / 1440) < 30
THEN  coalesce(convert(varchar(10), nullif((@Minutes % 525600) / 1440, 0)) + ' Day(s) ', '')
ELSE ''
END +
CASE WHEN ((@Minutes % 1440) / 60) <24
THEN coalesce(convert(varchar(10), nullif((@Minutes % 1440) / 60, 0)) + ' Hour(s) ', '')
ELSE ''
END +

CASE WHEN (@Minutes % 60) <60
THEN coalesce(convert(varchar(10), nullif(@Minutes % 60, 0)) + ' Minute(s)', '')
ELSE ''
END
as ElapsedTime
end```
For months i consulted my supervisor . Again thank you because now i had some thing in my hand before consulting . He told me there will be no start or end date . Just a number in int of elapsed time in min and it will have to be converted to string .

So i took (365 days / 12 months ) / 1440 ( min per day ) = 43800
it pretty much solved it .

thank you every one and thank you blindman .

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•