date time converting error in sqlserver( Error converting data type nvarchar to datetime. )

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008

i have a datetime string "01-10-2013 09:15 ص" when passing this value to a datetime parameter cause error

SQL

Error converting data type nvarchar to datetime.

plz help

Posted 9-Jan-13 22:47pm

rajin kp

Updated 27-Jul-16 3:44am

Add a Solution

Comments

faisal23 10-Jan-13 4:53am

Please provide more details ..

5 solutions

Solution 3

It might be that you are trying to concatenate an NVARCHAR with a DATETIME field. You need to use CONVERT http://msdn.microsoft.com/en-us/library/ms187928.aspx[ before doing so.

Posted 9-Jan-13 22:57pm

P.S Vijay

Solution 5

You have non-alphanumeric character(ص) in the string due to which you are facing this error.
Try it with any alphanumeric, it will work

Posted 9-Jan-13 23:04pm

Tejal S

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)

OriginalGriff · Accepted Answer · 2013-01-09T22:54:00

Solution 1

Firstly, don't pass it as a string - pass it as a DateTime via a Parametrized Query and you won't get a problem.

The problem is that SQL expects dates in yyyy-MM-dd format, and the non-English characters confuse it.

Passing dates as strings is not a good idea - convert them to DateTime values as soon as possible to get rid of any local Culture information that the user may have entered.

Posted 9-Jan-13 22:54pm

OriginalGriff

Comments

rajin kp 10-Jan-13 5:14am

DateTime d= DateTime.Parse(MeetingTime,CultureInfo.InvariantCulture);
cmdobj.Parameters.AddWithValue("@MeetingTime",d );
Now new error "The string was not recognized as a valid DateTime. There is a unknown word starting at index 17."

OriginalGriff 10-Jan-13 5:19am

Yes - the Arabic characters start at index 17.
You can't use InvariantCulture, you have to use the culture for the machine on which the date information was entered, it's the only way to be reasonably sure that the user input format matches what you are trying to translate it with.

rajin kp 10-Jan-13 6:09am

thanks.When i use Convert.ToDateTime(stringdate) problem solved.

OriginalGriff 10-Jan-13 6:13am

You're welcome!

fjdiewornncalwe 10-Jan-13 9:22am

My 5.

Member 11012743 27-Jun-15 6:48am

i am running stored procedure from SQL SERVER than also and passing date as input still getting same error "Error converting data type nvarchar to datetime."

OriginalGriff 27-Jun-15 6:53am

Same problem...so same solution! :laugh:

Member 11012743 27-Jun-15 6:55am

what same solution?

OriginalGriff 27-Jun-15 7:10am

The one you are commenting on, that I posted here two years ago!

Member 11012743 27-Jun-15 7:13am

as i find problem than only i asked.

OriginalGriff 27-Jun-15 7:24am

The error message you get means that you are passing a string value to the stored procedure and converting it to a DATE or DATETIME value - but SQL can't understand the string value you passed.
The only solution for this is to go to where you call the SP in your application and fix it there, where you have the user locale info etc., by converting, error reporting, and passing only a DateTime value to your SP.

Exactly the same problem as the original OP, and exactly the same solution...

Member 11012743 27-Jun-15 7:29am

i define stored procedure parameter as DateTime and passing DateTimePicker value to it

OriginalGriff 27-Jun-15 7:42am

This is really a separate question - and probably better as one since code in these little text boxes doesn't work too well. :laugh:

So start a new question, and show the App code, the SP code, and the exact error message.
Post a link here and I'll have a look at it.

Member 11012743 27-Jun-15 9:18am

ok

Shanu2rick · Accepted Answer · 2013-01-09T22:55:00

Solution 2

Try Converting the string to DateTime.

C#

string datetime="01-10-2013 09:15 ص";
DateTime newdatetime = Convert.ToDateTime(datetime);

If you are still getting the error, Kindly share it.

Posted 9-Jan-13 22:55pm

Shanu2rick

Updated 9-Jan-13 22:56pm

v2

pranathis012 · Accepted Answer · 2013-01-09T23:03:00

Hi,

what you try to do is you directly pass string value with datetime parameter.
That is not a good way whatever your using datatypes in sql you must and should to pass the same values otherwise you face some convertion problem.
Please convert your string value to datetime using convert.

C#

string str="01-10-2013 09:15 ص";

DateTime dt1=Convert.ToDateTime(str);