We are excited to announce that the ASP.NET Forums are moving to the new Microsoft Q&A experience. Learn more >

## 12 replies

Last post Aug 01, 2013 04:07 AM by aoshi_kh

• aoshi_kh

Member

92 Points

406 Posts

### time calculation

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

• oned_gk

All-Star

52833 Points

15790 Posts

### Re: time calculation

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.

Programming to simplify, don't look for difficult way
• aoshi_kh

Member

92 Points

406 Posts

### Re: time calculation

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

• ramu.net

Contributor

2272 Points

586 Posts

### Re: time calculation

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";```

Thanks
Ramu
• aoshi_kh

Member

92 Points

406 Posts

### Re: time calculation

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

• mshoaiblibra

Contributor

2842 Points

767 Posts

### Re: time calculation

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

Please mark the replies as if they help or unmark if not.

M Shoaib Waheed
MCSD & MCPD [Web Development 4.0]
http://shoaib.pk
• oned_gk

All-Star

52833 Points

15790 Posts

### Re: time calculation

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

Programming to simplify, don't look for difficult way
• aoshi_kh

Member

92 Points

406 Posts

### Re: time calculation

#### mshoaiblibra

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?

• mshoaiblibra

Contributor

2842 Points

767 Posts

### Re: time calculation

#### aoshi_kh

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

Please mark the replies as if they help or unmark if not.

M Shoaib Waheed
MCSD & MCPD [Web Development 4.0]
http://shoaib.pk
• mshoaiblibra

Contributor

2842 Points

767 Posts

### Re: time calculation

#### aoshi_kh

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

Please mark the replies as if they help or unmark if not.

M Shoaib Waheed
MCSD & MCPD [Web Development 4.0]
http://shoaib.pk
• mshoaiblibra

Contributor

2842 Points

767 Posts

### Re: time calculation

#### aoshi_kh

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

Please mark the replies as if they help or unmark if not.

M Shoaib Waheed
MCSD & MCPD [Web Development 4.0]
http://shoaib.pk
• aoshi_kh

Member

92 Points

406 Posts

### Re: time calculation

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

• aoshi_kh

Member

92 Points

406 Posts

### Re: time calculation

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

#### aoshi_kh

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