ASP.Net SQL String with Date String and SQL Server 2005

Question

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

score 2 · Answer 1 · answered Nov 26 '13 at 10:59

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())
        {
            // ...
        }
    }
}

answered Nov 26 '13 at 10:59

Tim Schmelter

450,073
74
686
939

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 Nov 26 '13 at 11:14
@user1166905: I'm not familiar with it but this answer suggests that you can: http://stackoverflow.com/questions/13035353/using-sql-server-named-parameters-with-executestorequery-and-executestorecommand 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). – Tim Schmelter Nov 26 '13 at 11:25
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. – Thorsten Dittmar Nov 26 '13 at 11:39

score -1 · Answer 2 · edited Nov 26 '13 at 11:18

-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) "'

edited Nov 26 '13 at 11:18

user1166905

2,612
7
43
75

answered Nov 26 '13 at 11:00

Ted

3,985
1
20
33

score -1 · Answer 3 · answered Nov 26 '13 at 11:18

-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.

answered Nov 26 '13 at 11:18

user1166905

2,612
7
43
75

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 – Ted Nov 26 '13 at 12:07

ASP.Net SQL String with Date String and SQL Server 2005

3 Answers3