How to SELECT Two-Digit Year From SQLite Date?

By default, SQLite does not provide a way to format DATE or DATETIME columns as a two-digit year. Therefore, to SELECT a two-digit year in SQLite you can do the following:

1. Use strftime() to get four-digit year from a SQLite date (i.e. YYYY);
2. Use substr() on the result of the previous step to remove the first two digits from the year.

This would look like the following:

substr(strftime('%Y', date_col), 3, 2)

This would return a substring (of YYYY) starting from the third character and include a total of two characters. In this way, you can exclude the first two digits of the year in a YYYY-MM-DD formatted date. To demonstrate this, consider the following table of customers for example:

+----+--------------------+
| id | name  | date_birth |
+----+--------------------+
|  1 | John  | 1978-01-01 |
|  2 | Wayne | 2015-08-01 |
|  3 | Jane  | 2005-08-01 |
|  4 | Skye  | 1992-12-01 |
+----+--------------------+

To SELECT a two-digit year from the date_birth column, you would use substr() and strftime() in the following way:

SELECT name, substr(strftime('%Y', date_birth), 3, 2) year_birth
FROM customer

This would result in the following:

+-------+------------+
| name  | year_birth |
+-------+------------+
| John  |     78     |
| Wayne |     15     |
| Jane  |     05     |
| Skye  |     92     |
+-------+------------+

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 `customer` (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(25) NOT NULL,
    date_birth DATE NOT NULL
);

INSERT INTO customer(`name`, `date_birth`) VALUES
('John', '1978-01-01'),
('Wayne', '2015-08-01'),
('Jane', '2005-08-01'),
('Skye', '1992-12-01');