How to Get the Day, Month, and Year from a Date in SQL

Most of the major RDBMSs have functions that enable us to extract the day, month, and year from datetime values.

Some RDBMSs provide multiple ways to do this, and others are more limited. Below are examples of extracting the day, month, and year from date values in some of the most popular RDBMSs.

MySQL

MySQL has several functions that can be used to extract the day, month, and year from a date. One of these is the EXTRACT() function:

SELECT 
    EXTRACT(DAY FROM '2035-12-19') AS Day,
    EXTRACT(MONTH FROM '2035-12-19') AS Month,
    EXTRACT(YEAR FROM '2035-12-19') AS Year;

Result:

+------+-------+------+
| Day  | Month | Year |
+------+-------+------+
|   19 |    12 | 2035 |
+------+-------+------+

In this case, I extracted each date part to its own field.

If you want all date parts to be returned in the same field, the DATE_FORMAT() function can do this for you.

MySQL also has a bunch of function that return specific date parts from a date, such as DAY(), MONTH(), YEAR(), etc.

See Return the Day, Month, and Year in MySQL for more examples.

Oracle

Oracle has a TO_CHAR() function that can be used to extract date parts in their own fields, or all in a single field. Here’s an example of returning them in individual fields:

SELECT 
    TO_CHAR(DATE '2035-09-26', 'Day') AS Day,
    TO_CHAR(DATE '2035-09-26', 'DD') AS DD,
    TO_CHAR(DATE '2035-09-26', 'Month') AS Month,
    TO_CHAR(DATE '2035-09-26', 'YYYY') AS Year
FROM DUAL;

Result:

         DAY    DD        MONTH    YEAR 
____________ _____ ____________ _______ 
Wednesday    26    September    2035

See this Full List of Datetime Format Elements in Oracle for a list of format elements that can be used to format datetime values with this function.

Oracle also has an EXTRACT() function that works like MySQL’s function of the same name.

SQL Server

SQL Server has quite a large range of functions that can return date parts like day, month, and year.

The DATEPART() function is designed specifically for returning specified parts of a date:

DECLARE @date date = '2045-07-03'; 
SELECT 
    DATEPART(day, @date) AS DAY, 
    DATEPART(weekday, @date) AS WEEKDAY, 
    DATEPART(month, @date) AS MONTH, 
    DATEPART(year, @date) AS YEAR;

Result:

+-------+-----------+---------+--------+
| DAY   | WEEKDAY   | MONTH   | YEAR   |
|-------+-----------+---------+--------|
| 3     | 2         | 7       | 2045   |
+-------+-----------+---------+--------+

A similar function is DATENAME(), which can return day and month names as a string.

SQL Server also has functions such as DAY(), MONTH(), YEAR(), etc that return a specific date part.

And let’s not forget the FORMAT() function, which is perfect if you want to return all date parts in the same field.

See 6 Functions to Get the Day, Month, and Year from a Date in SQL Server for more examples.

PostgreSQL

PostgreSQL has a few functions that can return date parts from dates.

Here’s an example of the DATE_PART() function:

SELECT 
    DATE_PART('DAY', date '2035-10-30') AS "Day",
    DATE_PART('DOW', date '2035-10-30') AS "DOW",
    DATE_PART('DOY', date '2035-10-30') AS "DOY",
    DATE_PART('MONTH', date '2035-10-30') AS "Month",
    DATE_PART('YEAR', date '2035-10-30') AS "Year";

Result:

 Day | DOW | DOY | Month | Year 
-----+-----+-----+-------+------
  30 |   2 | 303 |    10 | 2035

PostgreSQL also has an EXTRACT() function that basically does the same thing, except with a slightly different syntax.

And the TO_CHAR() function can be used if you want to return multiple date parts in the same field.

SQLite

SQLite is more limited when it comes to date and time functions. However, you can still use the STRFTIME() function to extract the day, month, and year from a date:

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

Result:

01 12 2035

In this example, I’ve returned all date parts in the same field. But this isn’t necessarily required. If you want them returned in different fields, you can call STRFTIME() multiple times, each with a different format element:

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

MariaDB

MariaDB provides a great selection of functions that can return the day, month, and year from a datetime value.

Here’s an example of the DATE_FORMAT() function:

SELECT DATE_FORMAT('2023-07-25', '%W, %D %M %Y');

Result:

+-------------------------------------------+
| DATE_FORMAT('2023-07-25', '%W, %D %M %Y') |
+-------------------------------------------+
| Tuesday, 25th July 2023                   |
+-------------------------------------------+

That function allows us to return all date parts in a single field (although this isn’t required – you could just as easily return each date part in a different field by calling DATE_FORMAT() multiple times, each time with a different format string).

And like some of the others, MariaDB also has an EXTRACT() function that extracts a given date part.

And there’s also a large range of specific functions for each date part, such as DAY(), MONTH(), YEAR(), etc

See 11 Functions to Get the Day, Month, and Year from a Date in MariaDB for a full rundown.