Conversion failed when converting date and/or time from character string.

Question

1.40/5 (3 votes)

See more:

.NET3.0

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 4-Jan-12 6:07am

JawadHafiz

Updated 13-May-19 11:37am

Mehdi Gholam

v2

Add a Solution

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.

Member 11493863 1-Apr-15 1:15am

cmd = New SqlCommand("Insert into emp_details values(" & Txtempid.Text & ",'" + Txtempname.Text + "','" + Txtempadd.Text + "','" + Drpgen.SelectedValue + "','" + Txtempdob.Text.ToString() + "','" + Txtempdoj.Text.ToString() + "','" + DropDownList2.SelectedValue + "'," & Txtcontact.Text & ",'" + DropDownList1.SelectedValue + "','" + Txtctry.Text + "','" + Txtcity.Text + "'," & TxtZip.Text & ")", cn)
cmd.ExecuteNonQuery()

JawadHafiz 4-Jan-12 12:34pm

SQL Query

SELECT ReferAFriend.*
FROM ReferAFriend where subscribe = @subscribe and @datetype between @SDate and @EDate

my codebehind

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

s_magus 4-Jan-12 16:20pm

What are you attempting to accomplish with @datetype? What datatype is @datetype?

Pantelis Chatzinikolis 4-Jan-12 14:18pm

First, I would strongly recommend that you switch to DateTime.TryParse. That won't fix your issue but it's a better method to use. Can you post the string you're trying to convert? Furthermore make sure it doesn't equal DBNull.

7 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Andrew Rissing · Answer 1 · 2012-01-04T07:00:00

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.

ARBebopKid · Answer 2 · 2012-01-04T06:37:00

Solution 2

I think change

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

to

raf.EDate = Convert.ToDateTime(txtEnd.Text);

Posted 4-Jan-12 6:37am

ARBebopKid

Comments

JawadHafiz 4-Jan-12 12:44pm

still same and it seems error comes from sql query

Abhinav S · Answer 3 · 2012-01-04T06:47:00

Solution 3

Use DateTime.TryParse - in this case if your value is not DateTime you will not get an error.
Read more about this here[^].

Posted 4-Jan-12 6:47am

Abhinav S

Wendelius · Answer 4 · 2012-01-04T07:07:00

Solution 5

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.

Posted 4-Jan-12 7:07am

Wendelius

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

Monjurul Habib · Answer 5 · 2012-01-04T07:51:00

Solution 6

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)

Posted 4-Jan-12 7:51am

Monjurul Habib

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

JawadHafiz · Answer 6 · 2012-01-07T02:06:00

Solution 8

Coulumn name in Table was incorrect

Posted 7-Jan-12 2:06am

JawadHafiz

Member 14366976 · Answer 7 · 2019-05-13T11:37:00

Solution 9

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

Posted 13-May-19 11:37am

Member 14366976

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

Conversion failed when converting date and/or time from character string.

7 solutions

Solution 4

Solution 2

Solution 3

Solution 5

Solution 6

Solution 8

Solution 9

Add your solution here

Preview 0

Existing Members

...or Join us