0

I am doing some select statements to get some data from SQLite databases.

The tables have Start and End dates, the dates are in the format of "31/07/2015 3:00:42 PM". The strange thing is, when I execute the reader, and check it immediately after, the results view will have "{"String was not recognized as a valid DateTime."}"

The thing is that it DID successfully query between the two dates I gave it, even though it apparently doesn't think so.

Now where I became even more confused is when trying to query a second table that also has two Date columns, the query throws the same error, but this time it actually DOESN'T work.

So basically SQLite is querying two tables using a date range, thinks that it failed both times, but actually succeeds on one of the tables.

The queries look like:

SELECT * FROM ShiftDetailLog WHERE StartTime >='31/07/2015 2:00:00 PM' AND StartTime <='31/07/2015 4:00:00 PM' ORDER BY StartTime asc   <<<(This query WORKS)

and

SELECT * FROM DowntimeLog WHERE StartTime >='31/07/2015 1:00:00 PM' AND StartTime <='31/07/2015 4:00:00 PM' ORDER BY StartTime asc    <<<(This query DOES NOT work)

Both StartTime and EndTime are both 'datetime' columns in SQLite in both tables.

Edit: Also, when I try to do something like:

string something = reader[0].ToString();

it actually errors saying "String was not recognized as a valid DateTime"... this is an error thrown by mscorlib...

I'm not referencing a datetime object, or telling it to convert anything to a datetime object...

Edit 2:

I am copying the queries directly from my C# code and testing them in the SQLite Browser. The queries work perfectly in the browser. Really unsure of what is going on with this one.

Justin
  • 533
  • 1
  • 7
  • 18
  • Did you try passing the Date time in your query string ? Try enclosing date time inside DateTime function. refer to https://www.sqlite.org/lang_datefunc.html – Rahul Sharma Jul 31 '15 at 19:55
  • I just tried this, the query executes successfully but returns 0 results. – Justin Jul 31 '15 at 20:01
  • I can not run this one but I know this works for sure eq: select * from AccountInfo where dateOfExpense>DATE('2010-01-20') and dateOfExpense – Rahul Sharma Jul 31 '15 at 20:03
  • It absolutely has to be a datetime query. Simply querying between 2 days is not specific enough for this application. – Justin Jul 31 '15 at 20:08
  • Oh Yes, This is supposed to be DateTime, the example I gave was from one of my previous applications, since I am not close to my machine I can not run it to test. The idea was to wrap the string inside the date time function and pass it as a date time object and not a character string inside the query. – Rahul Sharma Jul 31 '15 at 20:29

0 Answers0