Perform range query on sqlite datetime column or on string column(which having dateTime)

Question

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)

Pang · Accepted Answer · 2015-08-06 00:45:36Z

1

Try filling the gaps with zeros:

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

edited Aug 6, 2015 at 0:45

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Aug 6, 2015 at 0:30

Anyone

111 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Brandon Zacharie · Accepted Answer · 2013-12-01 01:27:03Z

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.

answered Dec 1, 2013 at 1:27

Brandon Zacharie

2,4403 gold badges25 silver badges30 bronze badges

2 Comments

Pratik Over a year ago

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

Brandon Zacharie Over a year ago

Assuming a column type defined as TEXT all you need to do is provide a ISO formatted date string. See stackoverflow.com/questions/114983/…. There's example code in this post stackoverflow.com/questions/8099845/… demonstrating how to insert a row in to a table with a datetime column.

Collectives™ on Stack Overflow

Perform range query on sqlite datetime column or on string column(which having dateTime)

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related