CODEWITHSUNDEEP
sqlite
Aneesh Daniel
Aneesh Daniel

Reputation: 533

SQLite less than operator problem

I am using SQLite for a project and < symbol is not working in the query.

There is a table named Holidays which has a field of datatype datetime.

Suppose the table contains some dates of current year in the column HolidayDate.

SELECT HolidayDate
  FROM Holidays
 WHERE (HolidayDate >= '1/1/2011')
   AND (HolidayDate <= '1/1/2012')

The < symbol in the above query is not working. > symbol in the above query is working well.

Please help me.

Upvotes: 5

Views: 14936

Answers (2)

manji
manji

Reputation: 47978

Try:

SELECT HolidayDate
  FROM Holidays
 WHERE HolidayDate >= date('2011-01-01')
   AND HolidayDate <= date('2012-01-01')

(date format must be YYYY-MM-DD)

Upvotes: 16

Jan Hudec
Jan Hudec

Reputation: 76286

There is no datetime datatype in sqlite.

Sqlite only has 4 types:

  • integeral number
  • floating-point number
  • string (stored either as utf-8 or utf-16 and automatically converted)
  • blob

Moreover, sqlite is manifest-typed, which means any column can hold value of any type. The declared type is used for two things only:

  • inserted values are converted to the specified type if they seem to be convertible (and it does not seem to apply to values bound with sqlite_bind_* methods at all)
  • it hints the indexer or optimizer somehow (I just know it has trouble using indices when the column is not typed)

Even worse, sqlite will silently accept anything as type. It will interpret it as integeral type if it starts with "int", as string if it contains "char" or "text", as floating-point number if it is "real", "double" or "number" and as blob if it's "blob". In other cases the column is simply untyped, which poses no problem to sqlite given how little the typing means.

That means '1/1/2011' is simply a string and neither dates in format 'mm/dd/yyyy' nor dates in format 'dd/mm/yyyy' sort by date when sorted asciibetically (unicodebetically really).

If you stored the dates in ISO format ('yyyy-mm-dd'), the asciibetical sort would be compatible with date sort and you would have no problem.

Upvotes: 5

Related Questions