Gridview Sort Date Issue

I have a gridview that I want to be able to sort by date and also manipulate the color of the cells based on the date. In the mysql database I have that date field as a character field, and it would cause problems if i were to change it.

So the problem is the gridview is sorting it as a string and not a date. The column is a template field.

I am using a sqldatasource to connect. Was curiuos if way to fix this? Without having to manually create my dataset.

Thanks

Show code please.

This is the markup for my gridview

An example of how it would sort woudl be this.

3/1/2009
3/10/2009
3/5/2009

Code:

---

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
                    BackColor="#CCCCCC" BorderColor="#999999" BorderStyle="Solid" BorderWidth="3px"
                    CellPadding="4" CellSpacing="2" Font-Size="Small" ForeColor="Black"
                AllowPaging="True" AllowSorting="True" DataSourceID="SqlDataSource1"
                PageSize="35" DataKeyNames="xospo,pcname" DataMember="DefaultView"
                        style="margin-right: 0px; margin-left: 0px;" ShowFooter="True">
                <FooterStyle BackColor="#CCCCCC" />
                <RowStyle BackColor="White" />
                <Columns>
                    <asp:CommandField ButtonType="Button" ShowEditButton="True" HeaderText="Edit" />
                    <asp:BoundField DataField="xospo" HeaderText="Quote #" SortExpression="xospo"
                        ReadOnly="True" HtmlEncode="False" />
                    <asp:BoundField DataField="xostky" HeaderText="Cust #"
                        SortExpression="xostky" ReadOnly="True" />
                    <asp:BoundField DataField="xosoldtoname" HeaderText="Cust. Name"
                        SortExpression="xosoldtoname" ReadOnly="True" />
                    <asp:BoundField DataField="updatedate" HeaderText=" PB Update Date"
                        HtmlEncode="False" ReadOnly="True" SortExpression="updatedate" />
                    <asp:BoundField DataField="pcname" HeaderText="PC Name" ReadOnly="True"
                        SortExpression="pcname" />
                    <asp:BoundField DataField="xosm" HeaderText="Rep" SortExpression="xosm"
                        ReadOnly="True" />
                    <asp:TemplateField HeaderText="Results" SortExpression="result">
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList2" runat="server"
                                DataSourceID="SqlDataSource2" DataTextField="codedesc"
                                DataValueField="codedesc" SelectedValue='<%# bind("result") %>'>
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label1" runat="server" Text='<%# bind("result") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="# Units" SortExpression="units">
                        <EditItemTemplate>
                            <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("units") %>'
                                Width="56px"></asp:TextBox>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label2" runat="server" Text='<%# Bind("units") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Est Ship Date" SortExpression="estshipdate">
                        <EditItemTemplate>
                        <asp:Image ID="Image1"
                                runat="server" ImageUrl="~/cal.png" style="width: 16px" />
                            &nbsp;&nbsp; &nbsp;
                            <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("estshipdate")  %>'
                                Width="73px" ReadOnly="False"></asp:TextBox>&nbsp;
                            <ajaxToolkit:CalendarExtender ID="CalendarExtender3" runat="server" TargetControlID="textbox2"  PopupButtonID="image1" EnableViewState="False" >
                            </ajaxToolkit:CalendarExtender>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label3" runat="server" Text='<%# bind("estshipdate") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:TemplateField HeaderText="Prob %" SortExpression="prob">
                        <EditItemTemplate>
                            <asp:DropDownList ID="DropDownList3" runat="server" SelectedValue='<%# bind("prob") %>'>
                                <asp:ListItem></asp:ListItem>
                                <asp:ListItem>0</asp:ListItem>
                                <asp:ListItem>25</asp:ListItem>
                                <asp:ListItem>50</asp:ListItem>
                                <asp:ListItem>75</asp:ListItem>
                                <asp:ListItem>100</asp:ListItem>
                            </asp:DropDownList>
                        </EditItemTemplate>
                        <ItemTemplate>
                            <asp:Label ID="Label4" runat="server" Text='<%# Bind("prob") %>'></asp:Label>
                        </ItemTemplate>
                    </asp:TemplateField>
                    <asp:BoundField DataField="dvalue" HeaderText="Total $"
                        SortExpression="dvalue" HtmlEncode="False" ReadOnly="True" />
                    <asp:BoundField DataField="totalsell" HeaderText="$ Per Unit"
                        SortExpression="totalsell" ReadOnly="True" />
                    <asp:BoundField DataField="xepn" HeaderText="Model" SortExpression="xepn" ReadOnly="True" />
                    <asp:BoundField DataField="authuser" HeaderText="Changes" SortExpression="authuser" ConvertEmptyStringToNull="False" HtmlEncode="False" ReadOnly="True" />
                </Columns>
                <PagerStyle BackColor="#CCCCCC" ForeColor="Black" HorizontalAlign="Left" />
                <SelectedRowStyle BackColor="#000099" Font-Bold="True" ForeColor="White" />
                <HeaderStyle BackColor="Black" Font-Bold="True" ForeColor="White" />
                <EditRowStyle Font-Strikeout="False" />
            </asp:GridView>

---

The Gridview needs to know, somehow, that you've given it a date field. Add this to the column.


<asp:BoundField DataField="updatedate" HeaderText=" PB Update Date" DataFormatString="{0:MMMM dd yyyy}" HtmlEncode="false" HtmlEncode="False" ReadOnly="True" SortExpression="updatedate" />

Thanks for response. But it is a template field so I dont have that property i dont think?

It is the estshipdate field.

You may then be in a bit of a pickle. When specified in a bound field, the 'date' value should be recognized as a date. However, in a templatefield, it's custom. So the gridview just throws its hands up and says "OK, it's all strings to me."

The easiest way around this would be to use another date format such as

2009-04-13

So that it's sorted by year, then month, then date.

The other way is to use a SortExpression and to convert the string to a datetime and then proceed.
http://msdn.microsoft.com/en-us/library/hwf94875.aspx