-1

I have a project that is used by several clients but for one of them using sql server 2005 wherever any code that runs a sql statement which includes a date like so:

" AND ABS.AbsenceDate = '" + DateTime.Now.ToString("yyyy-MM-dd") + "'";

I get the error: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

This only happens for this one client/server, if I copy the actual sql string created and execute it in Sql Server on this server it runs ok. I created a test page to ensure the date string was correct and did indeed display as 2013-11-26 as expected. My assumption was that the app was sending the date as 2013-26-11 as this would cause the issue but then the test showed otherwise so leaves me stumped.

For info, this is on an English server so the regional setting is day/month/year and dates stored as year-month-day etc

Improve this question

3 Answers 3

Reset to default
2

Don't concatenate strings to build your sql string but sql-parameters. The main reason is to prevent sql-injection but it can also prevent localization- or conversion issues like this.

string sql = @"SELECT Columns 
               FROM dbo.tableName
               WHERE ...
               AND ABS.AbsenceDate = @AbsenceDate";

using(var con = new SqlConnection("ConnectionString"))
using(var cmd = new SqlCommand(sql, con))
{
    cmd.Parameters.AddWithValue("@AbsenceDate",  DateTime.Today);
    con.Open();
    using(var reader = cmd.ExecuteReader())
    {
        while(reader.Read())
        {
            // ...
        }
    }
}
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

The values don't come from text boxes or anything people can enter into. I am using EF's ExecuteStoreQuery in places so can't use parameters.
@user1166905: I'm not familiar with it but this answer suggests that you can: stackoverflow.com/questions/13035353/… Apart from that, consider that today the user can't change it but maybe tomorrow. Also, as i've mentioned, parameters don't only prevent sql-injection but also other issues and they are more also more efficient (query does not change all the time, the systems may be able to re-use it).
Well, if EF can't do it, it sucks, I'd say and this alone would be a reason for me not to use it.
-1

I have solved the issue and quite surprised at what the problem was. I was able to run the sql ok in management studio but I was logged in using Windows Authentication. I realised I should try with the account the app uses and when logged in as this I did get the error.

Turns out whoever set the account up had the Language set to British English. I changed it to English and voila problem solved.

Improve this answer

1 Comment

well, you didn't actually solve the problem. all code should work regardless of server cutlure. my advice would be to always use sql parameters and avoid implicit data type conversions
-1

Never mind the added security, Sql Parameters would solve your problem automatically.

Is there a date part for that date or is it irrelevant ?

An example solution would be the following:

" AND ABS.AbsenceDate = Convert(DateTime, '" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', 20) "'
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.