How to format time(0) SQL data field as AM/PM using GridView DataFormatString property

Question

I have a GridView control that binds to a time(0) SQL data field. I would like to render the output as AM/PM (e.g. 1:35 PM) using the DataFormatString property. I have tried assigning several different values but the compiler throws a "Input string was not in a correct format." error in most cases. So far I have tried the following values:
{0:hh:mm tt}
{0:hh:mm:ss tt}
{0:hh:mm t}
{0:hh:mm:ss t}
{0:t} - complies but does not convert the format
{0:tt} - complies but does not convert the format

                            <asp:GridView SkinID="ResultsGrid" ID="GridViewAlerts" style="margin-top:2%"
                                runat="server" DataKeyNames="ID" AutoGenerateColumns="False" OnPageIndexChanging="GridViewAlerts_PageIndexChanging"
                                OnSorting="GridViewAlerts_Sorting" OnRowCommand="GridViewAlerts_RowCommand"
                                OnDataBound="GridViewAlerts_DataBound">      
                                <Columns>
                                    <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID"
                                        ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Name" HeaderText="Name" SortExpression="Name"
                                        ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="10%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="10%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Description" HeaderText="Description" SortExpression="Description"
                                        ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="30%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="30%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Interval" HeaderText="Interval" SortExpression="Interval" ItemStyle-HorizontalAlign="Left"
                                        HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Run_At" HeaderText="At" SortExpression="Run_At" ItemStyle-HorizontalAlign="Left"
                                        HeaderStyle-HorizontalAlign="Left" DataFormatString="{0:t}"  ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Last_Run_Dt" HeaderText="Last Run" SortExpression="Last_Run_Dt" ItemStyle-HorizontalAlign="Left"
                                        HeaderStyle-HorizontalAlign="Left" DataFormatString="{0: MM/dd/yyyy}" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                      <asp:BoundField DataField="Next_Run_Dt" HeaderText="Next Run" SortExpression="Next_Run_Dt" ItemStyle-HorizontalAlign="Left"
                                        HeaderStyle-HorizontalAlign="Left" DataFormatString="{0: MM/dd/yyyy}" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Last_Result" HeaderText="Result" SortExpression="Last_Result" ItemStyle-HorizontalAlign="Left"
                                        HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="5%" >
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Active" HeaderText="Status" SortExpression="Active"
                                        ItemStyle-HorizontalAlign="Left" HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:BoundField DataField="Owner" HeaderText="Owner" SortExpression="Owner"
                                        ItemStyle-HorizontalAlign="Left"  HeaderStyle-HorizontalAlign="Left" ItemStyle-Width="5%">
                                        <HeaderStyle HorizontalAlign="Left" />
                                        <ItemStyle HorizontalAlign="Left" Width="5%" />
                                    </asp:BoundField>
                                    <asp:CommandField ButtonType="Image" SelectImageUrl="~/Images/gear.png" HeaderText="Settings" ShowSelectButton="true"  ShowHeader="True" ItemStyle-Width="5%" />
                                    <asp:TemplateField HeaderText="Start/Stop" ItemStyle-Width="5%">
                                        <ItemTemplate>
                                           <asp:ImageButton runat="server" ID="ImageButtonStartStop" ToolTip="Start or stop this alert" ImageUrl="~/Images/start.png"
                                                OnClientClick="GridViewAlerts_RowCommand" CommandName="StartStop" CausesValidation="false"
                                                CommandArgument="<%# ((GridViewRow) Container).RowIndex %>" />
                                        </ItemTemplate>
                                    </asp:TemplateField>

                                    <asp:TemplateField HeaderText="Run" ItemStyle-Width="5%">
                                        <ItemTemplate>                                        
                                            <asp:ImageButton runat="server" ID="ImageButtonRun"  ToolTip="Manually run this alert"  ImageUrl="~/Images/play.png"
                                                OnClientClick="GridViewAlerts_RowCommand" CommandName="Run" CausesValidation="false"
                                                CommandArgument="<%# ((GridViewRow) Container).RowIndex %>" />
                                        </ItemTemplate>
                                    </asp:TemplateField>
                                </Columns>
                             </asp:GridView>    


     ![screenshot][1]                   

Answer 1

As @JozefBenikovský pointed out in the comments, you have to use the TimeSpan format strings. This requires you to escape the : field in the format string (or whatever character you use as the separator).

Note that the TimeSpan DOES NOT have an AM/PM designator format field string. To have the AM/PM designator, you will need to cast your SQL Server Time type to a DateTime and then apply the standard DateTime format strings.

The sample below illustrates both concepts:

<div>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>" 
        SelectCommand="SELECT ProductId, StoreTime, cast(StoreTime as DateTime) as StoreTimeAsDateTime FROM junk.dbo.[Product]">
    </asp:SqlDataSource>
    <asp:GridView ID="GridView1" runat="server" 
        AutoGenerateColumns="False" 
        DataKeyNames="ProductID" DataSourceID="SqlDataSource1">
        <Columns>
            <asp:BoundField DataField="ProductID" 
                HeaderText="ProductID" 
                InsertVisible="False" ReadOnly="True" 
                SortExpression="ProductID" 
                DataFormatString="{0:D6}" />
            <asp:BoundField DataField="StoreTime" 
                HeaderText="StoreTime" 
                SortExpression="StoreTime" 
                DataFormatString="{0:hh\:mm\:ss}" />
            <asp:BoundField DataField="StoreTimeAsDateTime" 
                HeaderText="StoreTimeAsDateTime" 
                SortExpression="StoreTime" 
                DataFormatString="{0:hh:mm:ss tt}" />
        </Columns>
    </asp:GridView>
</div>

Answer 2

Make sure the field in the database is also in the datetime format. For example, the DataFormatString property does not work if the table field is in varchar format.