The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Question

0.00/5 (No votes)

See more:

Hi,

I'm getting "

C#

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

" error
while running the sql query as "

Select * from table where [Date]=CONVERT(VARCHAR,'" + DateTime.Now.Date.ToShortDateString() + "',111)"

"

As "111" is for yyyy/mm/dd format
"103" is for dd/mm/yyyy format
"101" is for mm/dd/yyyy format

but still sometimes work and sometimes not. As on my local it is working properly as I set the time of my system accordingly but when deployed on server it is not working. Is there a complete solution, how to use date in the query as in any format?

Thanks

What I have tried:

I've used all the format i.e

As "111" is for yyyy/mm/dd format
"103" is for dd/mm/yyyy format
"101" is for mm/dd/yyyy format

to convert the time but it works in some platform and some not

Posted 12-Nov-16 9:33am

binadi007

Updated 12-Nov-16 10:31am

Add a Solution

Comments

[no name] 12-Nov-16 15:42pm

The error message is perfectly clear. Check your server settings.

binadi007 12-Nov-16 15:50pm

what settings to check on server?

2 solutions

Solution 2

First of all, you need to check the value of DateTime.Now.Date.ToShortDateString(), you may get a surprise, sometimes.
The answer depend on computer culture settings.
Use the debugger to see the value as the program runs.
-----
You should learn to use the debugger as soon as possible. Rather than guessing what your code is doing, It is time to see your code executing and ensuring that it does what you expect.

The debugger allow you to follow the execution line by line, inspect variables and you will see that there is a point where it stop doing what you expect.
Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.
-----

Quote:
Select * from table where [Date]=CONVERT(VARCHAR,'" + DateTime.Now.Date.ToShortDateString() + "',111)"

Never do this, because it promote a user input to SQL command and can lead to a problem called SQL Injection.
SQL injection - Wikipedia[^]
SQL Injection[^]
The solution to SQL injection will also solve your problem as sql server will know that your date is a date.

Posted 12-Nov-16 10:31am

Patrice T

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 · 2016-11-12T09:54:00

Solution 1

Simple: never concatenate strings to form an SQL command- it's very dangerous, as well as causing conversion problems such as this. Always use parameterized queries instead, and pass the DateTime value directly without conversion. Your code will no longer be print took SQL Injection attacks, and your problem will disappear the same time.

Posted 12-Nov-16 9:54am

OriginalGriff

Comments

binadi007 12-Nov-16 23:38pm

Can u plz provide an example too, relating to your context
Thanks

binadi007 13-Nov-16 0:24am

is this a right way?
string sql = "Select * from table where [Date]=@Date";
SqlCommand sqlCommand = new SqlCommand(sql);
sqlCommand.Parameters.AddWithValue("@Date", DateTime.Now.Date.ToShortDateString());
int k = demoClass.Checkid(sqlCommand);

OriginalGriff 13-Nov-16 2:13am

It's better, but don't convert it to a string: the system understands DateTime values and passed them to SQL in the right format automatically, so no conversion is needed.
That way, there is no chance of confusion between the server date format and the local computer default date format.

binadi007 13-Nov-16 2:54am

In place of
sqlCommand.Parameters.AddWithValue("@Date", DateTime.Now.Date.ToShortDateString());
I used
sqlCommand.Parameters.AddWithValue("@Date", SqlDbType.DateTime2);

OriginalGriff 13-Nov-16 4:40am

Why?
Pass the value directly...

sqlCommand.Parameters.AddWithValue("@Date", DateTime.Now.Date);