如何使用GridView DataFormatString属性将时间(0) SQL数据字段格式化为AM/PM

Question

我有一个绑定到time(0) SQL数据字段的GridView控件。我希望使用DataFormatString属性将输出呈现为AM/PM (例如1:35 PM)。我尝试分配了几个不同的值，但是编译器抛出了一个“输入字符串格式不正确”。大多数情况下出错。到目前为止，我尝试了以下值：

 {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

正如@JozefBenikovsk在注释中指出的那样，您必须使用TimeSpan格式字符串。这要求您以格式字符串(或用作分隔符的任何字符)转义:字段。

注意到表示，TimeSpan 不具有AM/PM指示符格式字段字符串。要拥有AM/PM指示符，您需要将Server时间类型转换为DateTime，然后应用标准的DateTime格式字符串。

下面的示例说明了这两个概念：

<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

确保数据库中的字段也是日期时间格式。例如，如果表字段为varchar格式，则DataFormatString属性无法工作。