如何日期时间传递从C＃正确的sql？ [英] How to pass datetime from c# to sql correctly?

问题描述

我有一个表，并在它的日期时间的格式为：

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

2011-07-01 15:17:33.357

我把C＃日期时间，当我做了的ToString（）我得到了格式的日期时间的对象：

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

我想知道我是如何通过正确的，因为当我运行的SQL是我们$ C $传递正确的日期时间c将其不工作（即选择正确的日期时间）。我不能使用SQL事件探查器。

Im wondering how I correctly pass the correct datetime through because when I run the sql that is in our code it doesnt work (i.e. select the correct datetime). I cant use sql profiler.

这是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'

和

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

一个返回53条记录的其他收益69条记录。这怎么可能？当我通过日期时间（04/07/2011 7点19分58秒）ROM C＃为sql没有记录显示在所有！

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) rom c# to sql no records show up at all!

推荐答案

您已经正确地使用的DateTime 参数从<$ C值来完成它$ C>的DateTime ，所以它的的应的已经工作。忘掉的ToString（） - 因为这里没有使用

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.

如果是有区别的，这是最有可能在两种环境之间不同的precision做;也许选择一个舍入（秒，也许？），并使用它。同时请记住UTC /本地/未知（数据库没有日期的厚道的概念; .NET一样）。

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).

我有一个表，并在它的日期时间的格式为： 2011-07-01 15：17：33.357

I have a table and the date-times in it are in the format: 2011-07-01 15:17:33.357

请注意，日期时间的数据库的是，在任何该等格式;这是显示你善意的谎言只是你查询的客户端。它被存储为的号的（甚至说是一个实现细节），因为人类有这种奇怪的倾向没有意识到，你已经显示的日期是一样的 40723.6371916281 。愚蠢的人类。通过将其简单地作为一个日期时间贯穿始终，你不应该得到任何问题。

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＃正确的sql？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！