Get row where datetime column = today's date

[gmeax]

in my windows form i have a datetimepicer.

i want when i select a specific date from it then run a stored procedure and populate the datagridview.

i am not getting any recored when i select the date from datetimepicer.

my database is sql server.

sample LogDate data:

5/16/2013 11:20:26 AM
5/16/2013 11:20:46 AM
5/16/2013 11:20:50 AM
5/16/2013 11:21:06 AM

---------------------------------------------------------------------------------------------------------------------------------------------------
Using connection As New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXX;Data Source=192.168.0.1"),
command As New SqlCommand("SelectDeviceLog", connection),
adapter As New SqlDataAdapter(command)
command.CommandType = CommandType.StoredProcedure

command.Parameters.AddWithValue("@LogDate", DTP1.Value.Date)

Dim table As New DataTable

adapter.Fill(table)

DGV1.DataSource = table
End Using
--------------------------------------------------------------------------
-------------------------------------------------------------------------
ALTER PROCEDURE [dbo].[SelectDeviceLog]

@LogDate datetime

AS

SELECT UserId, LogDate from DeviceLogs

where LogDate = @LogDate

return
--------------------------------------------------------------

 

[darkstone]

Try to convert your date column into varchar. On vb just format your datetimepicker control.

 

[jmcilhinney]

This code:

command.Parameters.AddWithValue("@LogDate", [B]DTP1.Value.[U]Date[/U][/B])

is specifying the date portion from the DTP with the time portion zeroed to midnight. You have said yourself that your database table values contain a time portion too so none of them are going to match unless they are exactly midnight. You're going to have to either trim the time off the database values in your SQL code or else pass in a date range, e.g.

SELECT UserId, LogDate from DeviceLogs

where LogDate >= @LogDateStart and LogDate < @LogDateEnd

command.Parameters.AddWithValue("@LogDateStart", DTP1.Value.Date)
command.Parameters.AddWithValue("@LogDateEnd", DTP1.Value.Date.AddDays(1))

 

[InertiaM]

As jmilhinney said, you can strip the time from the values in the database, and then just send one parameter.

SELECT
  UserId,
  LogDate
FROM
  DeviceLogs
WHERE
  CAST (FLOOR (CAST (LogDate AS FLOAT)) AS DATETIME) = @LogDate