数据库日期/时间比较未产生预期结果? [英] Database Date/Time comparison does not yield expected result?

问题描述

我在使用C#和MS Access时遇到问题，我希望通过以下调用返回一条记录:

I am having a problem with C# and MS Access, where I expect the following call to return one record:

   c = Shift.Get(ProfileID, Start, null, null, null, null, null, null);

其中开始"是"2015年1月7日下午3:30:00"，配置文件ID是"****** 16732"，方法是:

Where Start is "1/7/2015 3:30:00 PM" and ProfileID is "******16732" and the method is:

   public static ObservableCollection<Shift> Get(string profileID, DateTime? start,
         DateTime? stop, string fullName, bool? closed, bool? archived = null,
         Database db = null, string sort="ASC")
    {
        OleDbCommand cmd = new OleDbCommand(
            "SELECT profiles.profile_id, profiles.full_name, shifts.start, " +
            "shifts.stop, shifts.start_log, shifts.stop_log, shifts.notes, " +
            "shifts.closed, shifts.archived FROM shifts, profiles WHERE " +
            (profileID != null ? "(shifts.profile_id=@profile_id) AND " : "") +
            (start.HasValue ? "(shifts.start>=@start) AND " : "") +
            (stop.HasValue ? "(shifts.stop<=@stop) AND " : "") +
            (fullName != null ? "profiles.full_name=@full_name AND " : "") +
            (closed.HasValue ? "shifts.closed=@closed AND " : "") +
            (archived.HasValue ? "shifts.archived=@archived AND " : "") +
            "(shifts.profile_id=profiles.profile_id) " +
            "ORDER BY shifts.start " + sort 
            );

        if (profileID != null)
            cmd.Parameters.AddWithValue("@profile_id", profileID);

        if (start.HasValue)
            cmd.Parameters.AddWithValue("@start", start.Value.ToString());

        if (stop.HasValue)
            cmd.Parameters.AddWithValue("@stop", stop.Value.ToString());

        if (fullName != null)
            cmd.Parameters.AddWithValue("@full_name", fullName);

        if (closed.HasValue)
            cmd.Parameters.AddWithValue("@closed", closed.Value);

        if (archived.HasValue)
            cmd.Parameters.AddWithValue("@archived", archived.Value);
        ....
        }

给出以下班次表:

profile_id  start               stop                    start_log           stop_log                notes   closed  archived
******45544 1/7/2015 3:30:00 PM 1/2/2015 11:30:00 PM    1/7/2015 3:06:02 PM 1/2/2015 11:32:40 PM    ""  Yes No
******12956 1/7/2015 3:30:00 PM 1/2/2015 9:00:00 PM     1/7/2015 3:08:10 PM 1/2/2015 9:15:29 PM   ""    Yes No
******17392 1/7/2015 2:00:00 PM 1/2/2015 11:30:00 PM    1/7/2015 1:46:07 PM 1/2/2015 11:33:09 PM    ""  Yes No
******16732 1/7/2015 3:30:00 PM 1/2/2015 6:30:00 PM     1/7/2015 3:08:38 PM 1/2/2015 6:35:03 PM   ""    Yes No
******15503 1/7/2015 2:00:00 PM 1/2/2015 10:00:00 PM    1/7/2015 1:46:43 PM 1/2/2015 10:01:24 PM    ""  Yes No
******14536 1/7/2015 3:30:00 PM 1/2/2015 11:30:00 PM    1/7/2015 3:04:12 PM 1/2/2015 11:35:19 PM    ""  Yes No

但是，我没有任何记录，也没有错误.这令人惊讶，因为我在SQL语句的WHERE子句中确实有一个shifts.start >= @start并且数据存在.

However, I get a return of no record, and no errors. That is surprising, because I do have a shifts.start >= @start in the WHERE clause of SQL Statement and the data exists.

请注意，由于混淆了ProfileID，因为它很敏感，并且开始日期在停止日期之后开始，这显然是错误的，但这是测试数据，并且与结果无关.如果未提供db，则存在默认的数据库连接.

Note, the ProfileID is obfuscated, because it is sensitive, and the start date started after the stop date, which is obviously wrong, but that is the test data and should have no bearing on the result. There is a default database connection if db is not supplied.

我不得不做一些编辑，希望我没有在任何地方打错打字.

I had to do some editing and hopefully that I didn't mis-type anywhere.

有任何线索吗?

推荐答案

您需要将DateTime作为DateTime对象而不是字符串传递(删除.ToString(). 这样，该命令将正确解析DateTime对象，以便Access将其识别为DateTime.

You need to pass the DateTime as a DateTime object instead of a string(remove the .ToString(). That way the command will parse the DateTime object correctly so Access recognizes it as DateTime.

这篇关于数据库日期/时间比较未产生预期结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！