getting datediff/timespan between records

Question

Let's say I have a basic asp.net C# web app where I enter the dates of different events, say oil changes, for example. And I want to track this for different vehicles. And then I want to calculate the difference in days between oil changes.

So for the most recent oil change, the datediff/timespan would calculate from today. But for previous oil changes, I want the datediff/timespan to calculate between each previous record in the recordset. I could, I suppose, create a field to put the number of days in there, but I think it should be easy to do programmatically, no?

Now, in classic ASP, I'd do something like this (in fact, this is what I do in the previous version of this little app):

<%set rs=conn.Execute("select vehiclename,changedate from oilchange order by changedate desc")
do until rs.EOF
dim recordchk,record
recordchk = record
date2=date1
date1=rs("changedate")
if record <> rs("vehcilename") then
record = rs("vehiclename")

end if%>

<%
if record <> recordchk then  
%>


<tr><td colspan="9"><hr color="black"/></td></tr>

<tr><td colspan="9"><b><%=rs("vehiclename") %></b></td></tr>
<tr><td valign="top" width="15%"><%=rs("changedate") %>&nbsp;<i>(<%=datediff("d",rs("changedate"),date()) %> days)</i></td>
</tr>

<%else%>

<tr><td valign="top" width="15%"><%=rs("changedate") %>&nbsp;<i>(<%=datediff("d",rs("changedate"),date2) %> days)</i></td>
</tr>

<%end if%>

<%rs.MoveNext
loop
rs.Close
%>

But I'm stumped how to do in in ASP.NET

For the different vehicles I have a repeater, then I nest a repeater inside to run through the oil change records for that vehicle. Runs through the list of oil changes just fine, but I can't figure out how to do the calculation to get me the days between oil changes.

So, here's my codebehind... (you can see that it's not oil changes, but guitar string changes.)

protected void guitarrepeateritemdatabound(object sender, RepeaterItemEventArgs e)
    {
        stringsEntities db = new stringsEntities();

        if ((e.Item.ItemType == ListItemType.Item) || (e.Item.ItemType == ListItemType.AlternatingItem))
        {
            Repeater StringChangeRepeater = (Repeater)e.Item.FindControl("StringChangeRepeater");

            HiddenField guitarIDfield = (HiddenField)e.Item.FindControl("guitarIDint");
            int guitarIDint = Convert.ToInt32(guitarIDfield.Value);
            Label changedate = (Label)e.Item.FindControl("changedate");

            TimeSpan timeBetweenChanges = (DateTime.Now - Convert.ToDateTime(changedate));



            var qrystringchangesview = (from s in db.stringchange_view
                                        where s.guitarid == guitarIDint
                                        orderby s.changedate descending
                                        select new
                                        {
                                            guitarID2 = s.guitarid,
                                            s.guitarname,
                                            s.stringname,
                                            s.changedate,
                                            s.gauge

                                        });

            StringChangeRepeater.DataSource = qrystringchangesview;
            StringChangeRepeater.DataBind();

and then my aspx page:

 <asp:Repeater ID="GuitarRepeater" runat="server"  OnItemDataBound="guitarrepeateritemdatabound">
<ItemTemplate>
 <tr>
  <td valign="top">
  <asp:HiddenField ID="guitarIDint" runat="server" Value='<%# Eval("guitarID") %>' />

   <asp:LinkButton OnClick="clicktheinstrument" ID="guitarname" runat="server" Font-Bold="true" CommandArgument='<%#Eval ("guitarID") %>' Text='<%# Eval("guitarname") %>' />

   <asp:Repeater runat="server" ID="StringChangeRepeater">
    <ItemTemplate>
    <table><tr><td style="width:100px">
    <asp:Label ID="changedate" runat="server" Text='<%# Eval("changedate","{0:MM/dd/yyyy}") %>'></asp:Label> 
      (<asp:Label ID="timeBetweenChangesLabel" runat="server"></asp:Label>)
       </td><td style="width:200px">
       <asp:Label ID="stringname" runat="server" Text='<%# Eval("stringname") %>'></asp:Label>
       </td><td style="width:100px">
       <asp:Label ID="gauge" runat="server" Text='<%# Eval("gauge") %>'></asp:Label>
       </td></tr></table>
      </ItemTemplate>
      </asp:Repeater>
       </td>
      </tr>
       </ItemTemplate>
       <AlternatingItemTemplate>
       <tr style="background-color: #F7F7F7">
       <td valign="top">
       <asp:HiddenField ID="guitarIDint" runat="server" Value='<%# Eval("guitarID") %>' />

       <asp:LinkButton OnClick="clicktheinstrument" ID="guitarname" runat="server" Font-Bold="true" CommandArgument='<%#Eval ("guitarID") %>' Text='<%# Eval("guitarname") %>' />

       <asp:Repeater runat="server" ID="StringChangeRepeater">
       <ItemTemplate>
       <table><tr><td style="width:100px">
       <asp:Label ID="changedate" runat="server" Text='<%# Eval("changedate","{0:MM/dd/yyyy}") %>'></asp:Label>
       (<asp:Label ID="timeBetweenChangesLabel" runat="server"></asp:Label>)
       </td><td style="width:200px">
      <asp:Label ID="stringname" runat="server" Text='<%# Eval("stringname") %>'></asp:Label>
       </td><td style="width:100px">
       <asp:Label ID="gauge" runat="server" Text='<%# Eval("gauge") %>'></asp:Label>
       </td></tr></table>
        </ItemTemplate>
        </asp:Repeater>
        </td>
        </tr>
       </AlternatingItemTemplate>
        </asp:Repeater>

If you are not traveling around the world between the oil changes then simple date math will produce the desired results. DateDiff(...) woould be sufficient unless I am missing something. — Ross Bush, Dec 17 '12 at 03:38
You can use the `TimeSpan` class. This will allow you to get the difference in Days, Months, Years or any other format you like. — Elad Lachmi, Dec 17 '12 at 03:41
I know this isn't the place to discuss this, but I went back to look at my questions. (I haven't asked many.) Some, just don't have any answers that worked. For one that did have an answer that worked, the person answered the question in the comment area. How do I promote that as the correct answer? — walstib, Dec 17 '12 at 04:08
You can answer your own question and mark it as accepted, that is perfectly acceptable on this site. — Scott Chamberlain, Dec 17 '12 at 04:40

score 0 · Accepted Answer · answered Jan 11 '13 at 16:30

well, I got an answer over at asp.net forums... thought I'd post it here to help anyone else who's doing a similar thing.

foreach (RepeaterItem item in StringChangeRepeater.Items)
            {
                if (item.ItemType == ListItemType.Item || (item.ItemType == ListItemType.AlternatingItem))
                {
                    HiddenField stringchangeIDHidden = item.FindControl("stringchangeID") as HiddenField;
                    int stringchangeID = Convert.ToInt32(stringchangeIDHidden.Value);

                    var getcurrentrecord = (from s in db.stringchange_view
                                            where s.ID == stringchangeID
                                            select new
                                            {
                                                s.changedate

                                            }).First();


                    Label timeBetweenChangesLabel = item.FindControl("timeBetweenChangesLabel") as Label;
                    DateTime changedate1 = Convert.ToDateTime(getcurrentrecord.changedate);
                    TimeSpan changedateSpan = changedateX - changedate1;
                    TimeSpan changedateSpan2 = changedate1 - Convert.ToDateTime(getcurrentrecord.changedate);
                    timeBetweenChangesLabel.Text = changedateSpan.Days.ToString();

                    changedateX = changedate1;



                }

            }

getting datediff/timespan between records

1 Answers1