How to use DateTime with SQL queries?

Question

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?

Exactly what error are you getting?

Nick Jones
– Nick Jones

2012-09-07 10:09:55 +00:00
Commented Sep 7, 2012 at 10:09 — Nick Jones
– Nick Jones, Commented Sep 7, 2012 at 10:09

Aghilas Yakoub · Accepted Answer · 2012-09-07 10:12:40Z

2

You can just try with my_time.Date

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

answered Sep 7, 2012 at 10:12

Aghilas Yakoub

29k5 gold badges49 silver badges51 bronze badges

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

1 Comment

Anonymous Over a year ago

This is what I needed - I was most the way there on my own, but thanks. :)

Nicolas Repiquet · Accepted Answer · 2012-09-07 10:10:13Z

0

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

Try using SqlDateTime instead.

answered Sep 7, 2012 at 10:10

Nicolas Repiquet

9,3273 gold badges36 silver badges54 bronze badges

Comments

podiluska · Accepted Answer · 2012-09-07 10:11:18Z

0

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

answered Sep 7, 2012 at 10:11

podiluska

51.6k7 gold badges102 silver badges105 bronze badges

Collectives™ on Stack Overflow

How to use DateTime with SQL queries?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related