time calculation

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

Answer 1

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.

Answer 2

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';

Answer 3

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.

Answer 4

User1248258412 posted

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

Answer 5

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.

Answer 6

User-1716253493 posted

<asp:Label ID="Label1" runat="server"
     Text='<%# string.Format("{0:HH:mm:ss}",TimeSpan.FromSeconds(Convert.ToInt32(Eval("Duration")))) %>'>
</asp:Label>

Answer 7

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?

Answer 8

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

Answer 9

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

Answer 10

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

Answer 11

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

Answer 12

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