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  gt   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  gt   2011-07-01 07 19 58 000    And  SELECT   FROM tbl webLogging  WHERE userid   1  AND DateAdded  gt   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

User · Answer

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

User · Answer

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

[c#] How to pass datetime from c# to sql correctly?

Examples related to c#

Examples related to sql

Examples related to datetime