using date in where cluse

[saleem.mukhtiar]

dear friends i want to filter data by date ... see the attach pictures, after using where clause i receicve 0 rows :(

[Mitja Bonca]

Use DataView class and RowFilter property of it, to specify the filter.
But before that fill the dataTable and use databinding (datatable has to be a binding source of datagridview control).

You can find an example here.
bye

[adam_k]

Since your check in date is actually a date+time you can't just go ahead and say check_in = '3/11/2012' because this translates to '3/11/2012 00:00:000'
You should make sure that either your filter gets into account only the date part or filter for check_in between '3/11/2012' and '3/12/2012'

Please share the code you've used in this form to see how you are filtering your data and offer more advice.

[saleem.mukhtiar]

yes i m using date and time picker .. and i need only date filter .. i means all the records which are coming in a date ..
here is the code

On load event ....
.
Imports System.Data.SqlClient
Imports System.IO
Public Class frmCheckInChecout
Private Sub frmCheckInChecout_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim sql As String
Dim obj As New dbclass
Dim tbl As New DataTable
sql = "select * from tblSchdule "
tbl = obj.GetRecords(sql)
With DataGridView1
.DataSource = tbl

On filter event
.
Private Sub btnGenrate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnGenrate.Click
Dim sql As String
Dim obj As New dbclass
Dim tbl As New DataTable
If CboSearch.Text = "Check In" Then
sql = "select * from tblSchdule where CheckIn like '" & DateTimePicker2.Value.ToShortDateString() & "'"
tbl = obj.GetRecords(sql)
With DataGridView1
.DataSource = tbl
.Columns(0).Width = 80
.Columns(0).HeaderText = "File No"
.Columns(1).Width = 130
.Columns(1).HeaderText = "Hotel Name"
.Columns(2).Width = 90
.Columns(2).HeaderText = "Room Type"
.Columns(3).Width = 90
.Columns(3).HeaderText = "Room Size"
.Columns(4).Width = 90
.Columns(4).HeaderText = "Check In"
.Columns(5).Width = 90
.Columns(5).HeaderText = "Check Out"
.Columns(6).Width = 90
.Columns(6).HeaderText = "Total Nights"

End With

...

thanks for your time dear

[adam_k]

Like won't do the trick by itself. In order for like to work you need a wildcard character in your string ('%' or '_').
Since you are not using any your like is the same as =

I am not sure what db you are using, so I'll use VB to set the criteria although it can be done via SQL.

Change

sql = "select * from tblSchdule where CheckIn like '" & DateTimePicker2.Value.ToShortDateString() & "'"

to

sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"

[M.Waqas Aslam]

just change your query with this.

sql = "select * from tblSchdule where CheckIn between '" & DateTimePicker2.Value.ToShortDateString() & " and '" & DateTimePicker2.Value.ToShortDateString() & "'"

Regards

[saleem.mukhtiar]

no result waqas dear ... empty tale ... is its better to use mankes text box instead of DateAndTimePicker ???

[saleem.mukhtiar]

i m using SQL 2005 and VS 2008

[M.Waqas Aslam]

try this

sql = "select * from tblSchdule where CheckIn between '" & DateTimePicker2.Value.date.ToString() & " and '" & DateTimePicker2.Value.date.ToString() & "'"

Hope this will work fine.

Regards

[saleem.mukhtiar]

Dear friends this one worked perfectly .....
.
VB.NET Syntax (Toggle Plain Text)
sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"sql = "select * from tblSchdule where CheckIn >'" & DateTimePicker2.Value.ToShortDateString() & "' and CheckIn < '" & DateTimePicker2.Value.AddDays(1).ToShortDateString()&"'"

thans alot friends :-)

[adam_k]

You are welcome.
For future readers:
The above post shows the statement twice. You only need it once (stop before sql = in the second line)