HELP!! The conversion of a varchar type to a datetime data type resulted in....

[skran]

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"

[Begginnerdev]

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.

[skran]

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

[Jx_Man]

Try this :

dtpEqualTo.Value.ToString("yyyy-MM-dd")

[adam_k]

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.

[skran]

Thank you adam k!! It works!!

Eyxaristw! :)

[eldarzeynal]

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