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!

What is the type of the 'DateAdded' column in your table? Is it DateTime? Or some kind of varchar? — Marek Musielak, Jul 04 '11 at 11:11
Can you confirm that `DateAdded` has type `datetime`, not something like `[n]varchar(x)`? — Marc Gravell, Jul 04 '11 at 11:32
So why does the c# datetime come back with 0 records? (the debugger says that it is '04/07/2011 07:19:58') @Maras the datatype is datetime.... — Exitos, Jul 04 '11 at 11:35
Is you userid 1 at runtime when you check in the debugger? Just checking. — Lisa, Jul 04 '11 at 11:35
Well, for the sql samples the two dates you are using are not the same :) --- typo maybe? then first is using 1 july and the second 4 july. For the c# code: are you absolutely sure that the values you pass in are correct? — Eben Roux, Jul 04 '11 at 11:35
@Marc agree that the type in DB should not be a varchar to do this, but even if it was interpreting it as 7th April, surely some records should be returned because the > operator is used. — Lisa, Jul 04 '11 at 11:37
@Lisa yes, but *different* records, hence a different count. Actually I missed the point that @Eben makes re the 1st - easily missed! — Marc Gravell, Jul 04 '11 at 12:02

score 23 · Accepted Answer · answered Jul 04 '11 at 11:09

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.

Just not getting anything back. NOTE : I was only saying To.String was ... because I am looking in the debugger. But when I run the above code it just wont get any rows. How can I debug this? — Exitos, Jul 04 '11 at 11:21
@Pete2k well, is there any matching data? and is it a `>` vs `>=` issue? — Marc Gravell, Jul 04 '11 at 11:22

Eden · Answer 2 · 2019-04-11T07:29:48.547

8

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.

edited Apr 11 '19 at 07:29

answered Jul 04 '11 at 11:57

Eden

3,696
2
24
24

1

`.ToString("yyyy-MM-dd hh:mm:ss")` is not the best way to eliminate the ambiguity; the better way is to pass typed data, hence not being ambiguous. The code in the question is *better* than using `ToString(...)` with any particular format. – Marc Gravell Jul 04 '11 at 12:04
2

The code in question doesn't work for one reason or another, So the better solution would be making the code work and provide the expected results. Afterwards there is a lot of things to consider what would amount to even a "better" solution. – Eden Jul 04 '11 at 12:50
I use `.ToString(yyyy-MM-ddThh:mm:ss)`. Somehow this way I always know what to expect. Specifying type to SqlDateTime didn't work for me once, I've ended up having `2012-01-30 AM 10:15:30`, while in SQL it was `2012-01-30 10:15:30`. Not really sure why...just my 2 cents – nomail Feb 01 '13 at 15:22
2

.. and to that end, you should use `"yyyy-MM-dd HH:mm:ss"` as the format string (note the `HH` for 24hr instead of `hh` for 12hr), OR make sure you put `tt` after your `hh:mm:ss` to include AM/PM. – JoeBrockhaus Jun 06 '13 at 18:02
using `.ToString("yyyy-MM-dd hh:mm:ss")` will return `1:00:00` as the time, regardless if the original DateTime was 1pm or 1am. SQL will interpret that as 1am. As @JoeBrockhaus said, the correct time format is `HH:mm:ss` – mastazi Apr 11 '19 at 03:10

How to pass datetime from c# to sql correctly?

2 Answers2

Linked