Click here to Skip to main content
15,844,602 members
Please Sign up or sign in to vote.
1.40/5 (3 votes)
Conversion failed when converting date and/or time from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Conversion failed when converting date and/or time from character string.
Posted
Updated 13-May-19 12:37pm
v2
Comments
ARBebopKid 4-Jan-12 12:18pm    
Great error message. You probably have a bug somewhere.
JawadHafiz 4-Jan-12 12:21pm    
how to fix it?
ARBebopKid 4-Jan-12 12:26pm    
I'm not psychic. I can't see your code in my head.

But I can say that the string you're trying to convert isn't in the correct format.
Sergey Alexandrovich Kryukov 4-Jan-12 12:27pm    
Right :-)
--SA
devbtl 4-Jan-12 12:26pm    
put some piece of code here.

I would just start eliminating variables to what could be causing the problem:

1) Supply constant date times for the SQL parameters (SDate and EDate) (i.e. DateTime.Now.ToString("dd-MM-yyyy")) rather than the text boxes. If that doesn't fix it, continue.
2) Make sure the type of the SDate/EDate parameters match the datatype of the column they are compared to. If they are the same, continue.
3) Make sure the returning call to retrieve the data in .NET doesn't place the data into the wrong data type. If they are the same, you need to examine your system for further areas that can be eliminated for the source of the problem.


Happy hunting.
 
Share this answer
 
Comments
Wendelius 4-Jan-12 13:08pm    
Good answer, my 5.
I think change

raf.EDate = Convert.ToDateTime(txtEnd.Text).ToString("dd-MM-yyyy");


to

raf.EDate = Convert.ToDateTime(txtEnd.Text);
 
Share this answer
 
Comments
JawadHafiz 4-Jan-12 12:44pm    
still same and it seems error comes from sql query
Use DateTime.TryParse - in this case if your value is not DateTime you will not get an error.
Read more about this here[^].
 
Share this answer
 
To add to the solution given by Andrew Rissing, the code looks like you could have a varchar datatype in you table columns for dates. If that is true, I'd suggest using datetime2 and defining the parameter with that data type. This would ensure that you always handle dates correctly in the database. If you don't need/want the time portion, you can also use date.
 
Share this answer
 
Comments
JawadHafiz 4-Jan-12 13:15pm    
i have datetime DATATYPE and i need both date & time
Wendelius 4-Jan-12 13:18pm    
Ok, then there's no worry :) Did the original problem get solved?
JawadHafiz 4-Jan-12 13:26pm    
no, if anyone help
I am not sure where you are getting the error. Better you debug your code and find out where is the problem.
C#
raf.SDate = Convert.ToDateTime(txtStart.Text).ToString("dd-MM-yyyy"); raf.EDate = Convert.ToDateTime(txtEnd.Text).ToString("dd-MM-yyyy");


If your above code doesn't trough any exception, then try the following:

SQL
SELECT ReferAFriend.* FROM ReferAFriend where subscribe = @subscribe and CONVERT(DATETIME,@datetype,103) between CONVERT(DATETIME,@SDate,103) and CONVERT(DATETIME,@EDate,103)
 
Share this answer
 
Comments
JawadHafiz 4-Jan-12 13:57pm    
thanks, but same error
for
SELECT ReferAFriend.* FROM ReferAFriend where subscribe = @subscribe and CONVERT(DATETIME,@datetype,103) between CONVERT(DATETIME,@SDate,103) and CONVERT(DATETIME,@EDate,103)
Monjurul Habib 4-Jan-12 14:16pm    
did you debug??
JawadHafiz 4-Jan-12 14:01pm    
Thanks,
but same error
Coulumn name in Table was incorrect
 
Share this answer
 
I got the same problem and had to spend days before i solved it..
1. check your date format (if you used "Date" as your datatype in SQL then the date format should be "yyyy-MM-dd", that is based on your customization.
i used this and it worked for me. Click on the date picker, go to properties and use the following settings.

Date Format = Custom, 
CustomFormat = yyyy-MM-dd
 
Share this answer
 
Comments
CHill60 14-May-19 4:58am    
If you note Solution 8 posted by the OP 7 years ago, the problem was allegedly because they had used the wrong column name (@datetype presumably). There is also no evidence that the OP was using datapickers (txtStart, txtEnd).
However, if the OP had used a parameterised query then format would have been irrelevant

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900