0

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"

5
Contributors
6
Replies
19
Views
5 Years
Discussion Span
Last Post by eldarzeynal
0

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.

Edited by Begginnerdev: n/a

0

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

0

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.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.