How to store and get datetime value in SQLite.

Question

Hello, My table contains Birthdate field which has datatype as datetime. I want to get all records having birthday today. How can I get it?

Answer 1

Try this query:

SELECT * FROM mytable
WHERE strftime('%m-%d', 'now') = strftime('%m-%d', birthday)

Answer 2

Hi

SQLite has very poor support for storing dates. You can use the method suggested by Nick D above but bear in mind that this query will result in full table scan since dates are not indexed correctly in SQLite (actually SQLite does not support dates as a built-in type at all).

If you really want to do a fast query then you'll have to add a separate (integral) column for storing the birth day (1-31) and attach an index for it in the database.

If you only want to compare dates then you can add a single (INTEGER) column that will store the date UTC value (but this trick won't allow you to search for individual date components easily).

Good Luck

Liron Levi (author of the SQLite Compare diff/merge utility)