SQLite is less than an operator problem

Question

I use SQLite for the project and the <character does not work in the query.

There is a table called Holidays that has a datetime data type field.

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

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

The character in the above request does not work. > in the above query works well.

Please help me.

Answer 1

Try:

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

(date format must be YYYY-MM-DD )

Answer 2

Sqlite does not have a datetime data type.

Sqlite has only 4 types:

• integer number
• floating point number
• (saved as utf-8 or utf-16 and automatically converted)
• blob

In addition, sqlite is typical for display, which means that any column can contain a value of any type. The declared type is used for only two things:

• the inserted values ​​are converted to the specified type if they seem convertible (and this does not seem to apply to the values ​​associated with sqlite_bind_* methods in general)
• it somehow hints at an indexer or optimizer (I just know that it has problems using indexes when a column is not typed)

Moreover, sqlite will silently perceive something as a type. He will interpret it as an integer type if it starts with "int", as a string, if it contains "char" or "text", as a floating point number, if it is "real", "double" or "number" and like a blob if it's a "blob". In other cases, the column is simply untyped, which is not a problem for sqlite, given how little typing is.

This means that "1/1/2011" is just a string and neither dates in the format "mm / dd / yyyy" nor dates in the format "dd / mm / yyyy" are sorted by date when sorting by ascetic (actually definitely).

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