SQLite - Extract year, month and day from date string

By xngo on June 27, 2019

In SQLite, you can extract year, month and day from a date string. You simply need to use the substr(X, Y, Z) to extract the information that you want. It returns a substring of input string X that begins with the Y-th character and which is Z characters long. Y should start with 1.

-- Extract year. Output: 2019
SELECT substr("2019-06-27", 1, 4);
 
-- Extract month. Output: 06
SELECT substr("2019-06-27", 6, 2);
 
-- Extract day. Output: 27
SELECT substr("2019-06-27", 9, 2);

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.