How to pass datetime from c# to sql correctly?

Question

I have a table and the date-times in it are in the format:

2011-07-01 15:17:33.357 

I am taking a c# DateTime when I do a .ToString() on the object I am getting a DateTime in the format:

04/07/2011 06:06:17 

I'm wondering how I correctly pass the correct DateTime through because when I run the SQL that is in our code it doesn't work (i.e. select the correct DateTime). I can't use SQL profiler.

This is the code:

//looks to if a user has had any activity in within the last time specified         public bool IsUserActivitySinceSuppliedTime(int userId, DateTime since)         {             //get everything since the datetime specified [usually 5 hours as this is              //how long the session lasts for             string sql = "SELECT * FROM tbl_webLogging WHERE userid = @userid AND DateAdded > @sinceDateTime";              SqlParameter sinceDateTimeParam = new SqlParameter("@sinceDateTime", SqlDbType.DateTime);             sinceDateTimeParam.Value = since;              SqlCommand command = new SqlCommand(sql);             command.Parameters.AddWithValue("@userid", userId);             command.Parameters.Add(sinceDateTimeParam);               using (SqlDataReader DataReader = GetDataReader(command))             {                 if (DataReader.HasRows)                 {                     return true;                 }                 else                 {                     return false;                 }             }           } 

UPDATE*******************

I have run the following on the data:

SELECT * FROM tbl_webLogging  WHERE userid = 1  AND DateAdded > '2011-07-01 07:19:58.000' 

And

SELECT * FROM tbl_webLogging  WHERE userid = 1  AND DateAdded > '04/07/2011 07:19:58' 

One returns 53 records the other returns 69 records. How can this be? And when I pass the DateTime (04/07/2011 07:19:58) from c# to SQL no records show up at all!

Answer 1

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.

Answer 2

I had many issues involving C# and SqlServer. I ended up doing the following:

1. On SQL Server I use the DateTime column type
2. 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.