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');
Hope you found this post useful. It was published . Please show your love and support by sharing this post.