VB.net GROUP By Date ignore time

Question

Currently trying to group by date but ignoring the time thats presented it as my dgv groups it by the time aswell, i've looked around and am struggling to adapt to work. This is the original code that works but groups by date/time.

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

Any idea's?

Cheers, Pete

score 0 · Answer 1 · answered Jul 06 '17 at 09:55

0

Use CONVERT(DATE, r_datetime). The date datatype doesn't have a time component.

answered Jul 06 '17 at 09:55

HoneyBadger

14,750
3
34
48

score 0 · Accepted Answer · answered Jul 06 '17 at 09:56

0

First, you should always parameterize all your variables.

Second, if you want to group by date, you can't have the datetime in the select list, you'll have to do something like this:

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)

answered Jul 06 '17 at 09:56

Zohar Peled

79,642
10
69
121

Works like a treat - just another simple one, the data i have is for the 02/07/2017 and 04/07/2017 - is it simple to get it to show say for a date that doesnt have data to give it a quantity of 0, so in the dgv it'll show 01/07/2017, 02/07/2017, 03/07/2017..... ? – Peter James Jul 06 '17 at 10:12
First, I'm [glad to help](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) . Second, you might want to look at [this link](https://stackoverflow.com/questions/38218157/get-all-day-of-month-then-mapping-data-to-specific-date/38219556#38219556). – Zohar Peled Jul 06 '17 at 10:30

VB.net GROUP By Date ignore time

2 Answers2