1

net.dll for performing operations on sqlite. i am facing issue for sqlite database like i am created column as Text name as DateTime and storing date string into it. i want entries which are in between some date range, but i am not able to execute query.

used query for type of DateTime Column as "Date": **

SELECT * FROM TableName WHERE ColumnName BETWEEN '1800-1-1' AND '2010-12-12'

**

used query for type of DateTime Column as "String":

**

SELECT * FROM MD5_HEADER WHERE Sent BETWEEN '4/1/2007 12:01:00 AM' AND '12/1/2010 4:05:36 AM'

**

if anyone having solution regarding this issue please help me.

(c#, Sqlite.net.dll, framework3.5)

BenMorel
  • 34,448
  • 50
  • 182
  • 322
Pratik
  • 267
  • 4
  • 16

2 Answers2

1

Try filling the gaps with zeros:

SELECT * FROM TableName WHERE ColumnName BETWEEN '1800-01-01' AND '2010-12-12'
Pang
  • 9,564
  • 146
  • 81
  • 122
Anyone
  • 11
  • 1
0

First, SQLite doesn't have a date type. It stores dates as either text or integer. The first query looks correct assuming your column is of type text since ISO 8061 formatted dates have lexicographical order. It just looks like your missing a semicolon at the end of the statement.

Brandon Zacharie
  • 2,320
  • 2
  • 24
  • 29
  • But it checks based on string hence result shows wrong value. and main how to add datetime format in sqlite. (using c#) Because it does not accept dateTime.ToString() as a input for dateTime – Pratik Dec 04 '13 at 05:51
  • Assuming a column type defined as `TEXT` all you need to do is provide a ISO formatted date string. See http://stackoverflow.com/questions/114983/given-a-datetime-object-how-do-i-get-a-iso-8601-date-in-string-format. There's example code in this post http://stackoverflow.com/questions/8099845/datetimes-and-net-with-sqlite demonstrating how to insert a row in to a table with a datetime column. – Brandon Zacharie Dec 05 '13 at 06:12