In SQLite, we can use the strftime() function to return datetime values in our chosen format.

Therefore, we can use it to extract the day, month, and year from a date.

Examples

Here’s an example of extracting the day, month, and year all in a single field:

SELECT strftime('%d %m %Y', '2035-12-01');

Result:

01 12 2035

In the next example, we return each date part in a separate field:

SELECT 
    strftime('%d', '2035-12-01') AS "Day",
    strftime('%m', '2035-12-01') AS "Month",
    strftime('%Y', '2035-12-01') AS "Year";

Result:

Day  Month  Year
---  -----  ----
01   12     2035

We can also add our own format elements if we want:

SELECT strftime('%d/%m/%Y', '2035-12-01');

Result:

01/12/2035

Or we could remove all spaces/format elements to produce a numeric-style date:

SELECT strftime('%Y%m%d', '2035-12-01');

Result:

20351201