SQLite - select row by year only?

6
votes

How can I select rows from a table by year only?

SELECT * FROM speckdata AS s WHERE DATE_FORMAT(s.localdate, '%Y') = '2014')

I could get the result in MySQL

Now on sqlite I get this error,

SQLiteManager: Likely SQL syntax error: SELECT* FROM speckdata AS s WHERE DATE_FORMAT(s.localdate, '%Y') = '2014') [ near ")": syntax error ] Exception Name: NS_ERROR_FAILURE Exception Message: Component returned failure code: 0x80004005 (NS_ERROR_FAILURE) [mozIStorageConnection.createStatement]

Any ideas?

EDIT:

If I change it to,

WHERE DATE(s.localdate, '%Y') = '2014'

It return no result.

BTW, the localdate is in date-time format such as 2014-10-09 14:59:53

2
sqlsqlite
SELECT * FROM speckdata AS s WHERE DATE_FORMAT(s.localdate, '%Y') = '2014' - sqluser
Are you sure you have 2014 year in localdate? - sqluser
it is in date-time format such as 2014-10-09 14:59:53 - Run

2 Answers

11
votes

Try this:

SELECT * FROM speckdata AS s WHERE strftime('%Y', s.localdate) = '2014'
5
votes

Since DATE_FORMAT wouldn't work, maybe you can try an alternative solution

SELECT * FROM speckdata AS s WHERE DATE(s.localdate) LIKE '2014%'

P.S. You called DATE instead of DATE_FORMAT in your edit, I don't know it's intended or an accident on you.