Question Comparing dates PLEASE HELP!!

[kat333]

Hi! I am new to this forum and to programing in visual basic .net! I am sure there is an easy solution but I am stuck!! I have a question:

I have a program with a SQL 2008 backend with a table called p_timesheets inside of which is a field called tsDate stored as date.

I am able to pull the table into a datatable on my timesheet form. I have at the top of my timesheet form, a datetime picker. I need to compare the date selected in the date picker to the datatable and only show the entries that match the date. Here is what I have so far....

'Create Connection
        Dim sqlConn As New SqlConnection()
        sqlConn.ConnectionString = ("SERVER=XXXXX; DATABASE=XXXXX; Integrated Security=True")
        Try
            sqlConn.Open()
        Catch sqlError As Exception
            MessageBox.Show(sqlError.Message, "Connection Error")
        End Try
        If sqlConn.state = 1 Then
            Me.Text = "Connected!"
        End If
        Dim sqlComm As New SqlCommand
        Dim DTselectdate As Date
        DTselectdate = DteTimesheetDate.Text
        sqlComm.Connection = sqlConn

        'Create DataTable
        sqlComm.CommandText = "select tsdate as Date, starttime as Start_Time, arrivaltime as Arrival_Time, endtime as End_Time, comments as Comments from p_timesheet"

Can anyone help me please?!?!?
THANK YOU!!

 

[jmcilhinney]

When working with a DateTimePicker you should almost never be using the Text property. That is the String that gets displayed to the user. What you're interested in is the Value property, which is a Date. If you're using that Date to filter data when you retrieve it:

Dim command As New SqlCommand("SELECT SomeColumns FROM SomeTable WHERE DateColumn = @DateColumn", connection)

command.Parameters.AddWithValue("@DateColumn", myDateTimePicker.Value)

If you only want to use the date part and not the time then it would be Value.Date instead of just Value.

If you're using the Date to filter data that you've already retrieved:

myDataTable.RowFilter = String.Format("DateColumn = #{0:M/dd/yyyy}#", myDateTimePicker.Value)

 

[cjard]

remember that dates are stored as decimal numbers of days since some event.
01 Jan 1900 is stored as 0,
02 jan 1900 is stored as 1,
12:00 noon on 03 jan 1900 is stored as 2.5
18:00 on 04 jan 1900is stored as 3.75

Like all decimal comparisons 3 is not equal to 3.75 so if your table contains 04-jan-1900 18:00 you WONT find it by setting a filter of "= 04 jan 1900"

use a range (column > x and column < y)

 

[JohnH]

remember that dates are stored as decimal numbers of days since some event.
01 Jan 1900 is stored as 0,
02 jan 1900 is stored as 1,
12:00 noon on 03 jan 1900 is stored as 2.5
18:00 on 04 jan 1900is stored as 3.75

Like all decimal comparisons 3 is not equal to 3.75 so if your table contains 04-jan-1900 18:00 you WONT find it by setting a filter of "= 04 jan 1900"

use a range (column > x and column < y)

If the dates in db is stored as date part only (time = 0) you can also compare = with similar selected date value as jmcilhinney explained.