CODEWITHSUNDEEP
c#sqldatetimesql-server-ce
Anonymous
Anonymous

Reputation: 6251

How to use DateTime with SQL queries?

How should I be using c# datetime values in my C# queries?

Up until now I've been doing the following:

Datetime my_time = new DateTime(2012, 09, 05);
cmd.Parameters.AddWithValue("@date", my_time);
cmd.CommandText = "SELECT * FROM sales WHERE date = @date";

Which has been working fine until this morning when I've been getting null errors when retrieving values from the results set.

I changed my date parameter to grab the short date string from my datetime value like this:

cmd.Parameters.AddWithValue("@date", my_time.ToShortDateString());

And it appears to be working fine again.

What is the correct way of doing this? Obviously I'm converting my datetime value to a string, only (presumably) for the database to convert it back to a datetime before evaluating my equality where clause, which seems a little pointless.

Edit:

I'm using SQL Server Compact Edition and the date field in my example has a precision setting of 8 (date, excluding time right?). Does that mean I was passing the full date with 00:00:00.000 on the end before? How might I pass a datetime value to the database without including the time, without converting to a string first?

Upvotes: 0

Views: 2677

Answers (3)

Aghilas Yakoub
Aghilas Yakoub

Reputation: 29000

You can just try with my_time.Date

cmd.Parameters.AddWithValue("@date", my_time.Date);

Upvotes: 2

podiluska
podiluska

Reputation: 51514

You're not converting the date to a string - AddWithValue knows it is a date, but it is a datetime, so it has a time component. Do your sales have a time component to them?

 SELECT * FROM sales WHERE DateDiff(d, @date, date) = 0

Upvotes: 0

Nicolas Repiquet
Nicolas Repiquet

Reputation: 9265

Do not pass it as a string or will run in troubles with culture formatting.

Try using SqlDateTime instead.

Upvotes: 0

Related Questions