locked
time calculation RRS feed

  • Question

  • User1248258412 posted

    I have a database fields that keep the value of min, how can I select the fields and calculation the time into hours and min.

    eg: table keep value '3608'; I want to select it to display into 1hr8min

    Tuesday, July 30, 2013 10:02 PM

All replies

  • User-1716253493 posted

    Just think that 1 hour is equal with 60 minutes

    use 3608/60 as int to get hours

    then 3608 % 60 (modulo) operator to get minutes.

    Tuesday, July 30, 2013 10:11 PM
  • User1248258412 posted

    My table design is 1 stud can have many row of time, now I able to sum all the time up but show in min, I know should using %60 to calculate, but my question is I dont know how to show my data into hour and min format

    select sum(StudyLogFields) from Stud_StudyLog where StudId = 'A12345';

    Tuesday, July 30, 2013 10:46 PM
  • User-306750178 posted

    Hi,

    Use below code to convert seconds to time span

     TimeSpan ts = TimeSpan.FromSeconds(3608);
               
                string time = string.Empty;
                if (ts.Hours > 0)
                    time = ts.Hours.ToString() + "hr";
                if (ts.Minutes > 0)
                    time = time + ts.Minutes.ToString() + "min";
                if (ts.Seconds > 0)
                    time = time + ts.Seconds.ToString() + "sec";

    May this help you.

    Wednesday, July 31, 2013 1:11 AM
  • User1248258412 posted

    hi, i need it in SQL query, can sql select statement do that?

    Wednesday, July 31, 2013 1:13 AM
  • User516094431 posted

    aoshi_kh

    hi, i need it in SQL query, can sql select statement do that?

    Use this Query:

    Select CONVERT(VARCHAR(5),(ColumnName / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(ColumnName % 60)) + ' Minute' AS Time_Information

    FROM YouTableName

    For Example:
    Select CONVERT(VARCHAR(5),(3608 / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(3608 % 60)) + ' Minute' AS Time_Information

    Thanks.

    Wednesday, July 31, 2013 1:48 AM
  • User-1716253493 posted
    <asp:Label ID="Label1" runat="server"
         Text='<%# string.Format("{0:HH:mm:ss}",TimeSpan.FromSeconds(Convert.ToInt32(Eval("Duration")))) %>'>
    </asp:Label>

    Wednesday, July 31, 2013 2:29 AM
  • User1248258412 posted

    Use this Query:

    Select CONVERT(VARCHAR(5),(ColumnName / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(ColumnName % 60)) + ' Minute' AS Time_Information

    FROM YouTableName

     

    tested this way, but keep showing missing expression under the CONVERT(VARCHAR(5)

    Any idea?

    Wednesday, July 31, 2013 2:59 AM
  • User516094431 posted

    tested this way, but keep showing missing expression under the CONVERT(VARCHAR(5)

    Dear, below query working fine in my site. just copy and paste SQL and run it.

    DECLARE @tmp Table (timecolumn int)
    INSERT INTO @tmp VALUES (3608)
    INSERT INTO @tmp VALUES (8608)
    INSERT INTO @tmp VALUES (2308)

    Select CONVERT(VARCHAR(5),(timecolumn / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(timecolumn % 60)) + ' Minute' AS Time_Information
    FROM @tmp

    Note:

    Compare with your query may b you'r doing some mistake. if you still face issue then paste here your query. As well as, also tell me what is data type of time column.

    Thanks

    Wednesday, July 31, 2013 3:29 AM
  • User516094431 posted

    tested this way, but keep showing missing expression under the CONVERT(VARCHAR(5)

    Dear, below query working fine in my site. just copy and paste SQL and run it.

    DECLARE @tmp Table (timecolumn int)
    INSERT INTO @tmp VALUES (3608)
    INSERT INTO @tmp VALUES (8608)

    Select CONVERT(VARCHAR(5),(timecolumn / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(timecolumn % 60)) + ' Minute' AS Time_Information
    FROM @tmp

    Note:

    Compare with your query may b you'r doing some mistake. if you still face issue then paste here your query. As well as, also tell me what is data type of time column.

    Thanks

    Wednesday, July 31, 2013 3:29 AM
  • User516094431 posted

    tested this way, but keep showing missing expression under the CONVERT(VARCHAR(5)

    Dear, below query working fine in my site. just copy and paste SQL and run it.

    DECLARE @tmp Table (timecolumn int)
    INSERT INTO @tmp VALUES (3608)
    INSERT INTO @tmp VALUES (8608)

    Select CONVERT(VARCHAR(5),(timecolumn / 60) / 60) + ' Hour and ' + CONVERT(VARCHAR(5),(timecolumn % 60)) + ' Minute' AS Time_Information
    FROM @tmp

    Note:

    Compare with your query may b you'r doing some mistake. if you still face issue then paste here your query. As well as, also tell me what is data type of time column.

    Thanks

    Wednesday, July 31, 2013 3:30 AM
  • User1248258412 posted

    Hi mshoaiblibra, I tested in SQL server without problem, but I needed it in Oracle, when I test run on oracle, it will not able to execute.

    and my data type is using number(10),

    beside, do you know how the calculation if the design is like 3600000 should display as 1hr,  then 5400000 should display as 1hr30min

    Thanks in advance for helping

    Wednesday, July 31, 2013 3:40 AM
  • User1248258412 posted

    Anyone have idea how to resolve the problem in oracle database?

    Hi mshoaiblibra, I tested in SQL server without problem, but I needed it in Oracle, when I test run on oracle, it will not able to execute.

    and my data type is using number(10),

    beside, do you know how the calculation if the design is like 3600000 should display as 1hr,  then 5400000 should display as 1hr30min

    Thanks in advance for helping

    Thursday, August 1, 2013 4:07 AM