Database Date/Time comparison does not yield expec

2019-09-16 20:33发布

问题:

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

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

Given the following shifts table:

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

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.

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.

Any clues?

回答1:

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.