Calculate Number of Days in Grid Column

[Daniel]

The following represents the grid I am trying to construct:

DateReceived    DateShipped           Days
01/20/2011             NULL                      30
04/01/2011          04/15/2011               15
04/10/2011          04/12/2011                 3
01/15/2011          04/2/2011                   2

Columns one and two are populating successfully from my SqlDataSource.  No problem there.  Column three is intended to be a calculated value that displays the number of active days for a given date range.  For example, if the given start date was 4/1/2011 and the given end date was 4/30/2011, we would calculate 30, 15, 3 and 2 for the four rows in the grid.  That's because the first data record row represents an item that was received in January and has not been returned.  Therefore it was held for the entire 30 days of the month of April, 2011.  The next rows are self-explanatory.

To capture the date range I have a RadComboBox for month and another for year.

To summarize, my question is how do I implement an expression for column three to calculate the correct number of days? I'm happy to do this server side or client side.

*Note: I'm using mm/dd/yyyy notation.

[Princy]

Hello Daniel,

You can achieve this by using a GridTemplateColumn  with a label field and then set the text of the label field dynamically with the calculated value(date difference) in the ItemDataBound. Here is the sample code that I tried. Hope this helps.

aspx:

<telerik:GridTemplateColumn UniqueName="temp">
    <ItemTemplate>
     <asp:Label ID="lbl" runat="server"></asp:Label>
    </ItemTemplate>
</telerik:GridTemplateColumn>

C#:

protected void RadGrid1_ItemDataBound(object sender, Telerik.Web.UI.GridItemEventArgs e)

    {

        if (e.Item is GridDataItem)

        {

            GridDataItem item = (GridDataItem)e.Item;

            Label lbl = (Label)item["temp"].FindControl("lbl");               

            string Shipped = (item["DateReceived"].Text);

            string order = (item["DateShipped"].Text);

            if (order .ToString() == "NULL")

                {

                    lbl.Text = "30";

                     return;

                }

            else

            {

              int date = Convert.ToDateTime(Shipped).Subtract(Convert.ToDateTime(order)).Days;       

               lbl.Text = date.ToString();              

           }

       }

    }


Thanks,
Princy.

[Daniel]

Thanks Princy.  That's a really helpful response.

However, I'm receiving an error "

String was not recognized as a valid DateTime.

Line 100: else Line 101: { Line 102: int date = Convert.ToDateTime(Shipped).Subtract(Convert.ToDateTime(order)).Days; Line 103: lbl.Text = date.ToString(); Line 104: }

I double checked and my database query is indeed returning valid datetime.  And my columns for DateReceived & DateShipped are formatted correctly:

        <telerik:GridBoundColumn DataField="DateReceived" DataType="System.DateTime" 
            FilterControlAltText="Filter DateReceived column" HeaderText="DateReceived" 
            SortExpression="DateReceived" UniqueName="DateReceived">
        </telerik:GridBoundColumn>
        <telerik:GridBoundColumn DataField="DateShipped" DataType="System.DateTime" 
            FilterControlAltText="Filter DateShipped column" HeaderText="DateShipped" 
            SortExpression="DateShipped" UniqueName="DateShipped">
        </telerik:GridBoundColumn>

[Casey]

Hi Daniel,

Could you put a Breakpoint in the event and see what values are being used for Shipped and order? I tried Princy's suggestion and it worked.

Casey

[Daniel]

When I insert a breakpoint as you suggested I noticed that the variables are storing the following:

Shipped = "&npsp"
Order = null

Seems like my query may be returning unwanted records from the database.

[Casey]

Daniel,

It looks like once you get the data issue resolved that Princy's suggestion should give you the functionality you desire. 

Good luck!

Casey

[Daniel]

Indeed.  Thank you both for all your help!