Database Date/Time comparison does not yield expected result?

Question

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?

Why are you converting the date to a string when you add the parameter? — Mark PM, Jan 03 '15 at 17:54
when using query with datetime in msaccess you need to put the date within # sign as follows: where date =#07/01/2015 03:30:00# — Tomer Klein, Jan 03 '15 at 18:00
@MarkPM No particular reason, but I see without calling `ToString()` works too, and is probably preferred. Will change to this. @TomerKlein adding pound sign using parameterized queries causes an OleDBException. — YouAreSalty, Jan 03 '15 at 18:29
@MarkPM I already removed the ToString() and it works perfectly. I wonder if it internally just calls ToString() or does "it" recognize DateTime and automatically get's the correct dateformat irrespective of local? — YouAreSalty, Jan 03 '15 at 18:35

score 0 · Answer 1 · answered Jan 03 '15 at 18:39

0

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.

answered Jan 03 '15 at 18:39

Mark PM

2,909
14
19

I should have clarified earlier, that doing this seem to work and gives no errors, but the original behavior of not returning a record is still present. – YouAreSalty Jan 03 '15 at 18:41
I copied the value straight from the debugger as `{1/7/2015 3:30:00 PM}` so I believe so.... – YouAreSalty Jan 03 '15 at 18:53
I take that backthis does not work and causes `a data type mismatch in criteria expression`. Not sure why it seemed to work earlier, but I confirmed it does not work. – YouAreSalty Jan 03 '15 at 19:53
Is start a datetime type for the shifts table? – Mark PM Jan 03 '15 at 20:01
Yes, the `start` column in the `shifts` table in MS Access is a Date/Time type. – YouAreSalty Jan 03 '15 at 20:49

Database Date/Time comparison does not yield expected result?

1 Answers1

Linked