一个单元格中日期与查询不同的Gridview表示形式

Question

我的网格视图显示的日期与它使用的查询不同：

​

​

​

​

所有的日期都是我在查询中决定的约定。但是，第一个日期(星期一)是在网格视图上的错误的预防。我看不出为什么会这样。

下面是Gridview (它使用的查询非常长)：

        <asp:GridView CssClass="hoursGrid" ID="hoursReportGridView" runat="server" AutoGenerateColumns="False" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataSourceID="SqlDataSource2" OnRowDataBound="hoursReportGridView_OnRowDataBound">
            <Columns>
                <asp:BoundField DataField="Person" HeaderText="Person" SortExpression="Project" />
                <asp:BoundField DataField="Project" HeaderText="Project" SortExpression="Project" />
                <asp:BoundField DataField="Sprint" HeaderText="Sprint" ReadOnly="True" SortExpression="Sprint" />
                <asp:BoundField DataField="Story" HeaderText="Story" SortExpression="Story" />
                <asp:BoundField DataField="Task" HeaderText="Task" SortExpression="Task" />
                <asp:BoundField DataField="Monday" HeaderText="Monday" ReadOnly="True" SortExpression="Monday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Tuesday" HeaderText="Tuesday" ReadOnly="True" SortExpression="Tuesday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Wednesday" HeaderText="Wednesday" ReadOnly="True" SortExpression="Wednesday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Thursday" HeaderText="Thursday" ReadOnly="True" SortExpression="Thursday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Friday" HeaderText="Friday" ReadOnly="True" SortExpression="Friday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Saturday" HeaderText="Saturday" ReadOnly="True" SortExpression="Saturday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Sunday" HeaderText="Sunday" ReadOnly="True" SortExpression="Sunday" ItemStyle-HorizontalAlign="Right" />
                <asp:BoundField DataField="Total" HeaderText="Total" ReadOnly="True" SortExpression="Total" ItemStyle-HorizontalAlign="Right" />
            </Columns>
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
            <SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
            <SortedAscendingCellStyle BackColor="#FFF1D4" />
            <SortedAscendingHeaderStyle BackColor="#B95C30" />
            <SortedDescendingCellStyle BackColor="#F1E5CE" />
            <SortedDescendingHeaderStyle BackColor="#93451F" />
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ApplicationServices %>" SelectCommand="
     SELECT RowType AS RowType
    ,Person AS Person
    ,Project AS Project
    ,Sprint AS Sprint
    ,Story AS Story
    ,Task AS Task
    ,MondayHours AS Monday
    ,TuesdayHours AS Tuesday
    ,WednesdayHours AS Wednesday
    ,ThursdayHours AS Thursday
    ,FridayHours AS Friday
    ,SaturdayHours AS Saturday
    ,SundayHours AS Sunday
    ,TotalHours AS Total
FROM (
    SELECT '1' AS RowType
        ,'' AS Person
        ,'' AS Project
        ,'' AS Sprint
        ,'' AS Story
        ,'' AS Task
        ,(
            CASE 
                WHEN DATEPART(dw, @startDateParam) = 2
                    THEN CONVERT(VARCHAR(10), @startDateParam, 111)
                ELSE ''
                END
            ) AS MondayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 1, @startDateParam)) = 3
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 1, @startDateParam), 111)
                ELSE ''
                END
            ) AS TuesdayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 2, @startDateParam)) = 4
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 2, @startDateParam), 111)
                ELSE ''
                END
            ) AS WednesdayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 3, @startDateParam)) = 5
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 3, @startDateParam), 111)
                ELSE ''
                END
            ) AS ThursdayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 4, @startDateParam)) = 6
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 4, @startDateParam), 111)
                ELSE ''
                END
            ) AS FridayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 5, @startDateParam)) = 7
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 5, @startDateParam), 111)
                ELSE ''
                END
            ) AS SaturdayHours
        ,(
            CASE 
                WHEN DATEPART(dw, DATEADD(DAY, 6, @startDateParam)) = 1
                    THEN CONVERT(VARCHAR(10), DATEADD(DAY, 6, @startDateParam), 111)
                ELSE ''
                END
            ) AS SundayHours
        ,'' AS TotalHours
    --

    UNION ALL

    --
    SELECT '4' AS RowType
        ,DTH.PointPerson AS Person
        ,PDT.[Name] AS Project
        ,(
            CASE 
                WHEN TSK.NAME IS NULL
                    THEN NULL
                WHEN SPT.[Name] + ' - ' + SPT.[Description] IS NULL
                    THEN 'KanBan'
                ELSE SPT.[Name] + ' - ' + SPT.[Description]
                END
            ) AS Sprint
        ,COALESCE(STY.[Number], NSS.IncidentNumber) AS Story
        ,TSK.[Name] AS Task
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 2
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS MondayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 3
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS TuesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 4
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS WednesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 5
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS ThursdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 6
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS FridayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 7
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SaturdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 1
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SundayHours
        ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
    FROM DailyTaskHours DTH
    LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
    LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
    LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
    LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
    LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
     WHERE DTH.PointPerson LIKE @userParam 
     AND ActivityDate &gt;= @startDateParam 
     AND ActivityDate &lt;= @endDateParam
    GROUP BY DTH.PointPerson
        ,PDT.[Name]
        ,SPT.[Name]
        ,SPT.[Description]
        ,STY.[Number]
        ,NSS.IncidentNumber
        ,TSK.[Name]
    HAVING SUM(DTH.[Hours]) > 0
    --

    UNION ALL

    --
    SELECT '3' AS RowType
        ,DTH.PointPerson AS Person
        ,'' AS Project
        ,'' AS Sprint
        ,'' AS Story
        ,'' AS Task
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 2
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS MondayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 3
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS TuesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 4
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS WednesdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 5
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS ThursdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 6
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS FridayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 7
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SaturdayHours
        ,CAST(SUM(CASE 
                    WHEN DATEPART(dw, DTH.ActivityDate) = 1
                        THEN DTH.[Hours]
                    ELSE 0
                    END) AS VARCHAR(20)) AS SundayHours
        ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS TotalHours
    FROM DailyTaskHours DTH
    LEFT JOIN Task TSK ON DTH.TaskId = TSK.PK_Task
    LEFT JOIN Story STY ON TSK.StoryId = STY.PK_Story
    LEFT JOIN NonScrumStory NSS ON DTH.NonScrumStoryId = NSS.PK_NonScrumStory
    LEFT JOIN Sprint SPT ON STY.SprintId = SPT.PK_Sprint
    LEFT JOIN Product PDT ON STY.ProductId = PDT.PK_Product
     WHERE DTH.PointPerson LIKE @userParam 
     AND ActivityDate &gt;= @startDateParam 
     AND ActivityDate &lt;= @endDateParam
    GROUP BY DTH.PointPerson
    HAVING SUM(DTH.[Hours]) > 0
    ) AS My_View
--
UNION ALL
--
SELECT '2' AS RowType
    ,'All Personnel' AS Person
    ,'' AS Project
    ,'' AS Sprint
    ,'' AS Story
    ,'' AS Task
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 2
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Monday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 3
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Tuesday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 4
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Wednesday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 5
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Thursday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 6
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Friday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 7
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Saturday
    ,CAST(SUM(CASE 
                WHEN DATEPART(dw, DTH.ActivityDate) = 1
                    THEN DTH.[Hours]
                ELSE 0
                END) AS VARCHAR(20)) AS Sunday
    ,CAST(SUM(DTH.[Hours]) AS VARCHAR(20)) AS Total
FROM DailyTaskHours DTH
     WHERE ActivityDate &gt;= @startDateParam 
     AND ActivityDate &lt;= @endDateParam
ORDER BY RowType
    ,Person
    ,Project
    ,Sprint
    ,Story
    ,Task">
            <SelectParameters>
                <asp:QueryStringParameter Name="userParam" Type="String" DefaultValue="%" />
                <asp:QueryStringParameter Name="startDateParam" Type="String" />
                <asp:QueryStringParameter Name="endDateParam" Type="String" />
            </SelectParameters>
        </asp:SqlDataSource>

Answer 1

我认为，因为您在其他日子都使用了DATEADD，所以它以某种方式改变了结果。

试着使用

DATEADD(DAY, 0, @startDateParam)

对于MondayHours，并查看所有天的结果是否都是一致的。