How to Format the Year in SQLite Date to Two Digits?

By default, SQLite does not have any format specifier for a two-digit year representation. However, you can still SELECT a two-digit year based on either of the following use cases:

• When You Have Date Formatted as YYYY-MM-DD;
• When You Have a Custom Formatted Date.

If you would like to try any of the examples in this post yourself, then you can create the SQLite table and insert the dummy data (as used in the examples), 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');

Show Two-Digit Year With YYYY-MM-DD Formatted Date

If you want to SELECT SQLite date in the YY-MM-DD format when it is stored as YYYY-MM-DD, then you can simply use the substr() function to exclude the first two digits of the year (from a DATE or DATETIME column) like so:

substr(date_col, 3)

This would return a substring starting from the third character upto the length of the string, excluding the first two digits of the year in a YYYY-MM-DD formatted date. For example, consider the following table of customers:

+----+--------------------+
| 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 exclude the first two digits of the year from the date_birth column, you would use substr() in the following way:

SELECT name, substr(date_birth, 3) date_birth
FROM customer

This would result in the following:

+-------+------------+
| name  | date_birth |
+-------+------------+
| John  |  78-01-01  |
| Wayne |  15-08-01  |
| Jane  |  05-08-01  |
| Skye  |  92-12-01  |
+-------+------------+

Show Two-Digit Year With Custom Formatted Date

If you wish to have two-digit year with date formatted in some custom format (i.e. other than YYYY-MM-DD), then you can:

1. Format the year as two-digits separately using the strftime() function, and;
2. Concatenate the two-digit year to the other part of the formatted date.

For example, to format the date as dd/mm/yy, you could do something like the following:

SELECT name, strftime('%d/%m/', date_birth) || substr(strftime('%Y', date_birth), 3, 2) date_birth
FROM customer

This would result in the following:

+-------+------------+
| name  | date_birth |
+-------+------------+
| John  |  01/01/78  |
| Wayne |  01/08/15  |
| Jane  |  01/08/05  |
| Skye  |  01/12/92  |
+-------+------------+