How to pass datetime from c# to sql correctly?
You've already done it correctly by using a DateTime parameter with the value from the DateTime, so it should already work. Forget about ToString() - since that isn't used here.
If there is a difference, it is most likely to do with different precision between the two environments; maybe choose a rounding (seconds, maybe?) and use that. Also keep in mind UTC/local/unknown (the DB has no concept of the "kind" of date; .NET does).
I have a table and the date-times in it are in the format:
2011-07-01 15:17:33.357
Note that datetimes in the database aren't in any such format; that is just your query-client showing you white lies. It is stored as a number (and even that is an implementation detail), because humans have this odd tendency not to realise that the date you've shown is the same as 40723.6371916281. Stupid humans. By treating it simply as a "datetime" throughout, you shouldn't get any problems.
I had many issues involving C# and SqlServer. I ended up doing the following:
- On SQL Server I use the DateTime column type
- On c# I use the .ToString("yyyy-MM-dd HH:mm:ss") method
Also make sure that all your machines run on the same timezone.
Regarding the different result sets you get, your first example is "July First" while the second is "4th of July" ...
Also, the second example can be also interpreted as "April 7th", it depends on your server localization configuration (my solution doesn't suffer from this issue).
EDIT: hh was replaced with HH, as it doesn't seem to capture the correct hour on systems with AM/PM as opposed to systems with 24h clock. See the comments below.