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:
- Use
strftime()
to get four-digit year from a SQLite date (i.e.YYYY
); - 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');
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.