VB.net按日期分组忽略时间

Question

目前，我试着按日期分组，但忽略了我的dgv按时间分组的时间，我环顾四周，努力适应工作。这是原代码，但按日期/时间分组。

Dim sql2
    Try

        connect()
        sql2 = "SELECT r_datetime, SUM(r_quantity) FROM [r_repair] WHERE r_location = 'CNV' AND r_line =('" & computer_line & "') AND r_datetime >= @startdata2 AND r_datetime < @enddata2 AND r_area = 'DPMO(T)' GROUP BY r_datetime"
        Dim adapter2 As New SqlDataAdapter(sql2, con)

        With adapter2.SelectCommand.Parameters
            .Clear()

            .Add("@startdata2", SqlDbType.DateTime).Value = monthly_from.Value
            .Add("@enddata2", SqlDbType.DateTime).Value = monthly_to.Value.AddDays(1)
        End With

        Dim table_stencil As New DataTable()
        table_stencil.Locale = System.Globalization.CultureInfo.InvariantCulture
        adapter2.Fill(table_stencil)
        Me.bs_m_t.DataSource = table_stencil

        dgv_m_t.DataSource = bs_m_t

        disconnect()

        dgv_m_t.RowHeadersWidth = "28"

        dgv_m_t.Columns(0).HeaderText = "Date:"
        dgv_m_t.Columns(1).HeaderText = "Quantity:"

        dgv_m_t.EnableHeadersVisualStyles = False

    Catch ex As System.Exception
    End Try

有什么想法吗？

干杯，皮特

Answer 1

首先，应该始终参数化所有变量。

其次，如果您想按日期分组，则不能在选择列表中包含日期时间，您必须执行如下操作：

SELECT cast(r_datetime as date) as r_date, 
       SUM(r_quantity) as r_sum_quantity
FROM [r_repair] 
WHERE r_location = 'CNV' 
AND r_line = @r_line
AND r_datetime >= @startdata2 
AND r_datetime < @enddata2 
AND r_area = 'DPMO(T)' 
GROUP BY CAST(r_datetime as date)

Answer 2

使用CONVERT(DATE, r_datetime)。date数据类型没有时间组件。