9

i have date column in my database in dd-mm-yyyy format.

i want to search the data between two dates.

i tried 

SELECT * FROM t_data where orderdate between '01-05-2012 00:00:00' and '31-05-2012 23:59:59'

and

SELECT * FROM t_data where orderdate >= '05-05-2012' and  orderdate <='31-05-2012'

How to resolve this issue?

kannan
  • 317
  • 1
  • 4
  • 12
  • 1
    possible duplicate of [Sqlite convert string to date](http://stackoverflow.com/questions/4428795/sqlite-convert-string-to-date) – mvp Jun 06 '13 at 09:00
  • The answers to the question mvp linked will do what you want. You'd be much better off using a standard date format like ISO-8601 though. Then SQLite queries will work in the way you expect: http://www.sqlite.org/lang_datefunc.html – bsa Jun 07 '13 at 12:29

2 Answers2

13

use :

select * from tbl_name where Attribute_name between 'yyyy-mm-dd' and 'yyyy-mm-dd';

here Attribute_name is date_time column name

example :

select * from tbl_node where mydate between '2014-02-02' and '2014-02-06';

1|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:00:44
2|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:01:03
3|1|123|456|12eb-ab|1|1|254|123|19|2014-02-03 16:00:57
Puffin GDI
  • 1,702
  • 5
  • 27
  • 37
sanjeev
  • 332
  • 2
  • 10
2

The date format must be YYYY-MM-DD in SQLite.

Jorge Vieira
  • 2,784
  • 3
  • 24
  • 34