CODEWITHSUNDEEP
c#sql-servert-sqldatetimedatetime-format
user3888775
user3888775

Reputation: 113

Add days to DateTime returns value of 0?

This code counts number of records in an MSSQL table where a date is between today's date and today's date + 8 days, but it doesn't work; it returns a value of 0, but 2 is the right answer.

If I change DateTime.Now.AddDays to 7 or less it works as it should.

//Ordre klar til bestilling
command.CommandText = "SELECT COUNT(*) from bestillinger WHERE udlevering BETWEEN @date and @dateadd";
command.Parameters.AddWithValue("@date", DateTime.Now.ToString("dd/MM/yyyy"));
command.Parameters.AddWithValue("@dateadd", DateTime.Now.AddDays(+8).ToString("dd/MM/yyyy"));

con.Open();
command.ExecuteNonQuery();
string result0 = command.ExecuteScalar().ToString();

con.Close();
MessageBox.Show(result0);

if (result0 != "0")
{
    bestillingToolStripMenuItem.ForeColor = System.Drawing.ColorTranslator.FromHtml("#FF1919");
}

Upvotes: 2

Views: 368

Answers (2)

Joel Coehoorn
Joel Coehoorn

Reputation: 415705

Seven days from now, it's still September, and the date looks like this:

30/09/2014

Eight days from now, it's October, and the date looks like this:

01/10/2014

Pop quiz: does 01/10/2014 refer to October 1st, or January 10th? Obviously, you mean for it to refer to October 1st, but parts of the world (and likely Sql Server) will instead read that as January 10th.

The solution here is that you shouldn't pass date parameters as strings! Just removing the .ToString() calls entirely is probably enough to fix it in this case, but I prefer to be explicit about my database types:

command.Parameters.Add("@date", SqlDbType.DateTime).Value = DateTime.Today;
command.Parameters.Add("@dateadd", SqlDbType.DateTime).Value = DateTime.Today.AddDays(8);

Of course, the above fix assumes that you've done the sane thing in your database. You may have made the bad mistake of storing your dates as varchar fields in the first place. If you did that, Sql Server is comparing your fields as strings, and based on your example format anything early in the month is always going to be sorted before dates later in the month, even across different months. This means that your example end date came before your start date. If this is your problem, you'll need to add a new Date column to your table and write code to update your rows with the converted date values. You'll probably need to coordinate this with fixing other parts of your application that insert, update, and select on this field.

The main lesson to take away from this: NEVER treat dates as strings! This applies to every level of your application except the presentation tier.

Upvotes: 8

Marc Gravell
Marc Gravell

Reputation: 1062695

Don't treat dates/times as strings. Just:

command.Parameters.AddWithValue("@date", DateTime.Now);
command.Parameters.AddWithValue("@dateadd", DateTime.Now.AddDays(8));

The problem could well be date/time formats.

Note that you are actually executing it twice for no reason; you can remove the command.ExecuteNonQuery().

Finally, don't treat integers as strings:

int count = (int)command.ExecuteScalar();
if(count != 0)  { .... }

Upvotes: 12

Related Questions