Home > Forum > ASP.NET > need to calculate for 24 hours in asp.net

need to calculate for 24 hours in asp.net

by celv   on Oct 17, 2014   Category: ASP.NET  |  Views: 1131    |  Points: 5   |  Bronze 
  Reply
private void PopulateGrid()
{
string constr = ConfigurationManager.AppSettings["s"].ToString();

using (SqlConnection conn = new SqlConnection(constr))
{
string sqlQuery = "SELECT Id, CONVERT(VARCHAR(10),InTime,108) AS InTime , CONVERT(VARCHAR(10),OutTime,108) AS OutTime FROM aten where id='" + TextBox1.Text + "' and date between ' " + TextBox2.Text + "' and '" + TextBox3.Text+ "'";

using (SqlCommand cmd = new SqlCommand(sqlQuery, conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataSet ds = new DataSet();
da.Fill(ds);
this.gvEmployeesTime.DataSource = ds;
this.gvEmployeesTime.DataBind();

this.lblTotalHours.Text = string.Format(" {0}:{1}:{2}", total.Hours, total.Minutes, total.Seconds);
}
}
}
}
protected void OrdersDates_OnRowDataBound(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow)
{
string[] startTime = e.Row.Cells[1].Text.Split(':');
string[] endTime = e.Row.Cells[2].Text.Split(':');
TimeSpan t = new TimeSpan();
TimeSpan startTimeSpan = new TimeSpan(Convert.ToInt32(startTime[0]), Convert.ToInt32(startTime[1]), Convert.ToInt32(startTime[2]));
TimeSpan endTimeSpan = new TimeSpan(Convert.ToInt32(endTime[0]), Convert.ToInt32(endTime[1]), Convert.ToInt32(endTime[2]));
t = endTimeSpan - startTimeSpan;
total += t;
string time = string.Format("{0}:{1}:{2}", t.Hours, t.Minutes, t.Seconds);

e.Row.Cells[3].Text = time;
}
}


my data is

3 09:10:35 20:33:12
3 08:58:17 20:26:26
3 09:31:50 20:33:33


my output is
Total Hours = 9:52:29

it never show above one hour.i need to show 33 hours 52minutes

Bookmark and Share:
 
  User Reply  | Ask a question  |   Reply 
  Re :need to calculate for 24 hours in asp.net   
by Thamil
on Oct 17 2014 2:28AM
Points : 10
Gold 
Hi celv,

Could you explain more on your exact requirement
  Re :need to calculate for 24 hours in asp.net   
by celv
on Oct 17 2014 3:40AM
Points : 10
Bronze 
please refer my code..


this is my data from sql


id intime outtime
3 08:58:17 20:26:26
3 09:31:50 20:33:33

3 09:10:35 20:33:12


i need total worked hours
i got the result as 09:52:29 as output but my need is as 33 hours 52 minutes and 12 seconds

refer my c# code above
  Re :need to calculate for 24 hours in asp.net   
by Thamil
on Oct 17 2014 11:53AM
Points : 10
Gold 
Hi celv

Refer the below code and output shows as per your requirement

ASPX page:
<asp:GridView ID="GVEmpTimeDetails" runat="server" AutoGenerateColumns="False">
<Columns>
<asp:BoundField DataField="EmpID" HeaderText="EmpID" />
<asp:BoundField DataField="StartTime" HeaderText="StartTime (HH:mm:ss)" />
<asp:BoundField DataField="EndTime" HeaderText="EndTime (HH:mm:ss)" />
<asp:BoundField DataField="TotalHrs" HeaderText="TotalHrs (HH:mm:ss)" />
</Columns>
</asp:GridView>

Code Behind Page:
 protected void Page_Load(object sender, EventArgs e)
{
if(!IsPostBack)
{
GVEmpTimeDetails.DataSource = GetData();
GVEmpTimeDetails.DataBind();
}
}
private DataTable GetData()
{
DataTable dtEmp = new DataTable("Emp");
DataColumn EmpId = new DataColumn("EmpID", typeof(System.Int32));
DataColumn StartTime = new DataColumn("StartTime", typeof(System.String));
DataColumn EndTime = new DataColumn("EndTime", typeof(System.String));
DataColumn TotalHrs = new DataColumn("TotalHrs", typeof(System.String));
dtEmp.Columns.AddRange(new DataColumn[] { EmpId, StartTime, EndTime, TotalHrs });
dtEmp.Rows.Add("1", "09:10:35", "20:33:12","");
dtEmp.Rows.Add("1", "08:58:17", "20:26:26", "");
dtEmp.Rows.Add("2", "09:31:50", "20:33:33", "");
dtEmp.Rows.Add("2", "09:00:35", "19:36:31", "");
dtEmp.Rows.Add("2", "08:10:35", "21:19:10", "");
dtEmp.Rows.Add("3", "09:30:00", "18:33:46", "");
dtEmp.Rows.Add("3", "08:45:00", "17:36:45", "");
dtEmp.Rows.Add("3", "09:25:00", "19:50:17", "");
dtEmp.Rows.Add("3", "07:30:40", "20:40:15", "");
dtEmp.Rows.Add("3", "10:00:35", "21:10:00", "");

DataView view = new DataView(dtEmp);
DataTable distinctValues = view.ToTable(true, "EmpId");
DataTable dt = new DataTable();
foreach (DataRow rw in distinctValues.Rows)
{
int empid = Convert.ToInt32(rw[0]);
string expression = "empid="+empid;
DataRow[] foundRows = dtEmp.Select(expression);

TimeSpan totalhrs=new TimeSpan();
foreach (DataRow rwTime in foundRows)
{
TimeSpan ts;
string startTime = rwTime[1].ToString();
string EndTim = rwTime[2].ToString();
DateTime dt1 = DateTime.ParseExact(EndTim, "HH:mm:ss", new DateTimeFormatInfo());
DateTime dt2 = DateTime.ParseExact(startTime, "HH:mm:ss", new DateTimeFormatInfo());
ts = dt1.Subtract(dt2);
totalhrs += ts;
}
foreach (DataRow rwTime in foundRows)
{
if (totalhrs.Days != 0)
{
rwTime[3] = TimeSpan.FromDays(totalhrs.Days).TotalHours + totalhrs.Hours + ":" + totalhrs.Minutes + ":" + totalhrs.Seconds;
}
else
{
rwTime[3] = totalhrs.Hours + ":" + totalhrs.Minutes + ":" + totalhrs.Seconds;
}
}
}
return dtEmp;
}

Output:
EmpID	StartTime (HH:mm:ss)	EndTime (HH:mm:ss)   TotalHrs (HH:mm:ss)
--------------------------------------------------------------------------
1 09:10:35 20:33:12 22:50:46
1 08:58:17 20:26:26 22:50:46
2 09:31:50 20:33:33 34:46:14
2 09:00:35 19:36:31 34:46:14
2 08:10:35 21:19:10 34:46:14
3 09:30:00 18:33:46 52:39:48
3 08:45:00 17:36:45 52:39:48
3 09:25:00 19:50:17 52:39:48
3 07:30:40 20:40:15 52:39:48
3 10:00:35 21:10:00 52:39:48


Thanks
  Re :need to calculate for 24 hours in asp.net   
by celv
on Oct 19 2014 4:12AM
Points : 10
Bronze 
private DataTable GetData()
{

DataTable dtEmp = new DataTable("aten");
DataColumn Id = new DataColumn("Id", typeof(System.Int32));
DataColumn Name = new DataColumn("Name", typeof(System.String));
DataColumn Status = new DataColumn("Status", typeof(System.String));
DataColumn Section = new DataColumn("Section", typeof(System.String));
DataColumn Intime = new DataColumn("Intime", typeof(System.DateTime));
DataColumn Date = new DataColumn("Date", typeof(System.String));
DataColumn Outtime = new DataColumn("Outtime", typeof(System.DateTime));
DataColumn totalhours = new DataColumn("totalhours", typeof(System.String));
dtEmp.Columns.AddRange(new DataColumn[] { Id, Name, Status, Section, Intime, Date, Outtime, totalhours });
DataView view = new DataView(dtEmp);
DataTable distinctValues = view.ToTable(true, "Id");
DataTable dt = new DataTable();
foreach (DataRow rw in distinctValues.Rows)
{
int empid = Convert.ToInt32(rw[0]);
string expression = "empid=" + empid;
DataRow[] foundRows = dtEmp.Select(expression);
TimeSpan totalhrs = new TimeSpan();
foreach (DataRow rwTime in foundRows)
{
TimeSpan ts;
string inTime = rwTime[1].ToString();
string outTime = rwTime[2].ToString();
DateTime dt1 = DateTime.ParseExact(outTime, "HH:mm:ss", new DateTimeFormatInfo());
DateTime dt2 = DateTime.ParseExact(inTime, "HH:mm:ss", new DateTimeFormatInfo());
ts = dt1.Subtract(dt2);
totalhrs += ts;
}
foreach (DataRow rwTime in foundRows)
{
if (totalhrs.Days != 0)
{
rwTime[3] = TimeSpan.FromDays(totalhrs.Days).TotalHours + totalhrs.Hours + ":" + totalhrs.Minutes + ":" + totalhrs.Seconds;
}
else
{
rwTime[3] = totalhrs.Hours + ":" + totalhrs.Minutes + ":" + totalhrs.Seconds;
}
}
}
return dt;


}


it never fetch the data from sql

Recent Post

Latest Posts