SQLite query to return records earlier than a certain year?

[TimTheCoder]

I am trying to come up with an SQL query to return all the records earlier than a certain year with SQLite using DB Browser. I can't seem to come up with the right regex expressipn to use for filtering only the year from a row for comparison, below is the query am using. It is returning 0 records please help.

SELECT * FROM EMPLOYEES WHERE strftime('%Y', HireDate)<2003;

I am trying to get all records where the HireDate which is a column is earler than 2003, please help.

[Reverend Jim]

How are you storing the date? The answer depends on whether you are storing it as a string (and what date format the string is in) or a date/time. If you could post the query you used to create the database that would be very helpful. I could use it to create a test database to verify the query.

[Dani]

In SQLite, you can run this statement to get information about a specific table:

PRAGMA table_info(EMPLOYEES);

That will tell you the column type of each column in the table, and we'll be able to see the type of data the HireDate column stores. The thing is, that SQLite does not have a storage mechanism specifically for storing dates/times and so they are stored either as text or integers (unix time), and the strftime() function should be able to handle it either way.

Can you give us some examples of what the HireDate column looks like?

[AndrisP]

I think your mistake is comparing string and number

You should be use cast(strftime('%Y', HireDate) as number)<2003

[TimTheCoder]

Andrisp, it worked thanks a lot.