Hi all!
I have a user interface where I want to filter a datagridview.. I 've achieved to create filters but I 've failed to filter tha data by date.. I have a sql db and the column of date is of datetime type. When I try to filter the data by choosing a date in a datetimepicker I use this query:

If TodayToolStripMenuItem.Checked = True  Then
query = "Select * from INCIDENTS where DateReported='" & dtpEqualTo.Value.Date & "'"

The problem is that when I run the project I take the message "The conversion of a varchar type to a datetime data type resulted in an out of range value"

Check your database to make sure that the data type is set to date time.

If that is not the case, if you are using a string, parse as a date then pass into the string.

You can use Cdate to do this.

I m using a datetimepicker and I want to take the date the user choose in order to filter the datagridview.

Try this :


I've seen this error when the date passed as criteria is not in the same format as the server expects (SQL profile with regional settings MM-DD-YYYY and client passing date with format DD-MM-YYYY).
Try to SET DATEFORMAT DMY (replace DMY with the appropriate format as you datetimepicker's format) in the same command, just before your select, like this:

If TodayToolStripMenuItem.Checked = True  Then
query = "SET DATEFORMAT DMY Select * from INCIDENTS where DateReported='" & dtpEqualTo.Value.Date & "'"

PS: If I remember correctly the DATEFORMAT will be persistent for the session until changed or connection is closed.

Thank you adam k!! It works!!

Eyxaristw! :)

commented: Parakalw :) +9

Thank you very much !!!! i've been searching fix for this error. It worked !!!