How to SELECT Year From SQLite Date?

To SELECT only the year from a SQLite date, you can use the strftime() function with the %Y format specifier like so:

strftime('%Y', date_col)

For example, consider the following table of blog posts:

+----+---------------+---------------------+
| id | title         | date_posted         |
+----+---------------+---------------------+
|  1 | Lorem Ipsum   | 2020-01-01 00:00:00 |
|  2 | Proin ut Ante | 2021-05-01 00:00:00 |
|  3 | Fusce vitae   | 2015-12-01 00:00:00 |
+----+-----------------+-------------------+

To SELECT only the year from the date_posted column, you would use strftime() in the following way:

SELECT id, title, strftime('%Y', date_posted) year
FROM blog_post
ORDER BY year DESC

This would result in the following:

+----+---------------+------+
| id | title         | year |
+----+---------------+------+
|  2 | Proin ut Ante | 2021 |
|  1 | Lorem Ipsum   | 2020 |
|  3 | Fusce vitae   | 2015 |
+----+---------------+------+

If you would like to try this example yourself, then you can create the table and insert the data (as shown in the examples above), in the following way:

CREATE TABLE `blog_post` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title VARCHAR(25) NOT NULL,
    date_posted DATETIME NOT NULL
);

INSERT INTO blog_post(`title`, `date_posted`) VALUES
('Lorem Ipsum', '2020-01-01 00:00:00'),
('Proin ut Ante', '2021-05-01 00:00:00'),
('Fusce vitae', '2015-12-01 00:00:00');