Results 1 to 14 of 14
  1. #1
    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. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...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
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    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.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    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. #7
    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. #8
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Read my post. With a start date in addition to minutes, the datediff functions will account for different month lengths accurately.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    And for that matter, the method I supplied above assumes standard years, not leap years.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Hmm....The joke tags must be broken ;-)

  14. #14
    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
  •