1.11M Members

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

 
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"

 
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.

 
0
 

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

 
0
 

Try this :

dtpEqualTo.Value.ToString("yyyy-MM-dd")
 
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.

 
1
 

Thank you adam k!! It works!!

Eyxaristw! :)

Question Answered as of 2 Years Ago by Jx_Man, Begginnerdev and adam_k
 
0
 

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

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: