Calculate time duration in 2 columns

I want to calculate time in 2 column(column STATUSIN and column STATUSOUT) and place the value in column WORKINGHOUR as datetime.

This is the code to display data from the database:

<asp:Repeater ID="rptrDAILYDATAWH" runat="server">
            <HeaderTemplate>
                <table class="table">
                    <thead>
                        <tr>
                            <th>NIP</th>
                            <th>NAME</th>
                            <th>DEPARTMENT</th>
                            <th>IN</th>
                            <th>OUT</th>
                            <th>WORKINGHOUR</th>
                        </tr>
                    </thead>
                    <tbody>
            </HeaderTemplate>
            <ItemTemplate>
                <tr>
                    <th><%# Eval("NIP") %></th>
                    <td><%# Eval("NAME") %></td>
                    <td><%# Eval("DEPARTMENT") %></td>
                    <td><%# Eval("STATUSIN") %></td>
                    <td><%# Eval("STATUSOUT") %></td>
                    <td><%# Eval("WORKINGHOUR") %></td>
                </tr>
            </ItemTemplate>
            <FooterTemplate>
                    </tbody>
                </table>
            </FooterTemplate>
        </asp:Repeater>

This is the code on backend:

protected void BindDAILYDATWHARptr() {
  String CS = ConfigurationManager.ConnectionStrings["MANHOURConnectionString"].ConnectionString;
  using (SqlConnection con = new SqlConnection(CS)) {
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM DAILYDATAWH", con)){
      using (SqlDataAdapter sda = new SqlDataAdapter(cmd)) {
        DataTable dtDaily = new DataTable();
        sda.Fill(dtDaily);
        rptrDAILYDATAWH.DataSource = dtDaily;
        rptrDAILYDATAWH.DataBind();
      }    
    }
  }
}

Answer

You can access the DateTime of each rows STATUSIN and STATUSOUT, convert it to DateTime and get the difference

foreach (DataRow pRow in dtDaily.Rows)  
{  
  pRow["WORKINGHOUR"] = (pRow.Field<DateTime>("STATUSOUT") - pRow.Field<DateTime>("STATUSIN")).ToString();
}  

// After the rows are updated, Then bind your datasource.
rptrDAILYDATAWH.DataSource = dtDaily;
rptrDAILYDATAWH.DataBind();

(stOut -stIn) gives you TimeSpan that you can use to get TotalDays or other properties if you want.

If you want to save the changes, use the Update command.

sda.Update(dtDaily);

Advertisement

Answer