Error Date in SQL Query

Question

I have a problem with my SQL query.

The error is :

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

I tried to use the CONVERT function to remedy it but in vain.

public static List<string> Helper_Statistic_6(DateTime start, DateTime end) {
        DateTime dateStart = start;
        DateTime dateEnd = end;

        string query = "SELECT ... FROM ... WHERE DATE BETWEEN CONVERT(VARCHAR(10),'" + dateStart+ "',120) and CONVERT(VARCHAR(10),'" + dateEnd+ "',120) ";
}

Check your use of single-quote marks... are those fields varchar, or are they being cast as text by the use of quote marks? — Smandoli
– Smandoli, Commented Feb 20, 2014 at 16:45
Sorry, it's Sql Server 2008 R2 Example of date in my rows : 2002-12-30 00:00:00 (type smalldatetime) When i use this query in Sql Server : L.DO_DATE BETWEEN '01/01/2014' and '03/31/2014' it's works — Jayce
– Jayce, Commented Feb 20, 2014 at 16:54

Stephen Quan · Accepted Answer · 2014-02-20 21:26:04Z

1

I suspect you're using C# with Microsoft SQL Server.

In any case, to avoid the woes of code injection, one should try to use parametized SQL. Allow the compiler take care of marshalling a C# Date to a SQL Date.

EDIT: As per @marc_s suggestion, you should beware using reserved SQL keywords as column names, otherwise, protect them from being treated as SQL keywords by using [ ] symbols, i.e. [DATE] isntead of DATE.

I would expect the syntax to look like this:

public static void Run_Helper_Statistic_6(DateTime start, DateTime end)
{
    using (SqlCommand command = new SqlCommand(
        "SELECT ... FROM ... WHERE [DATE] BETWEEN @start and @end", connection))
    {
        command.Parameters.Add(new SqlParameter("start", start));
        command.Parameters.Add(new SqlParameter("end", end));
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            // ...
        }
    }
}

edited Feb 20, 2014 at 21:26

answered Feb 20, 2014 at 16:52

Stephen Quan

28k4 gold badges102 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

marc_s Over a year ago

Also, be aware that DATE is a keyword in SQL and thus makes a really bad column name - if that's really the name, it has to be put into square brackets: ... WHERE [Date] BETWEEN .... - or better yet - avoid those keywords as column names!

Jayce Over a year ago

I changed as you said Stephen Quan and now when I want to get the value of the reader, I have a cast problem between DateTime and String. to get the value, I do this : stat6.Date = DateTime.Parse(reader.GetString(0));

Jayce Over a year ago

I'm tired I think I just had to change in reader.getDateTime.

Collectives™ on Stack Overflow

Error Date in SQL Query

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related