3 Functions that Get the Day, Month, and Year from a Date in PostgreSQL

PostgreSQL provides us with several ways to get the day, month, and year from a date.

Three functions that immediately come to mind are; DATE_PART(), EXTRACT(), and TO_CHAR().

`EXTRACT()`

The EXTRACT() function retrieves subfields such as the year, month, hour, or minute, part from a date/time value.

Example:

SELECT EXTRACT(YEAR FROM date '2035-10-30');

Result:

We can return different date parts in separate fields by making three separate calls to this function:

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

Result:

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

Here, I returned the day of the month, the day of the week, the day of the year, the month, and the year from the date.

See How Extract() Works in PostgreSQL for more examples, and a list of field names that can be returned by this function.

`DATE_PART()`

The DATE_PART() function is the equivalent of EXTRACT(), but with a slightly different syntax.

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

This function requires that the first argument is provided as a string, and a comma separates the two arguments instead of the FROM keyword.

`TO_CHAR()`

The TO_CHAR() function is more flexible, in that it can be used to provide a wider variety of outputs. For example, we can use this function to return the day, month, and year all in one field:

SELECT TO_CHAR(date '2045-12-02', 'Day, DDth Month YYYY');

Result:

 Saturday , 02nd December  2045

We can use the fm template modifier to suppress any padding that may be applied:

SELECT TO_CHAR(date '2165-04-01', 'fmDay, fmDDth fmMonth YYYY');

Result:

 Monday, 1st April 2165

Also note that the th template modifier is smart enough to know whether st, nd, rd, or th should be appended.

Here’s an example that uses various calls to TO_CHAR() to output various date parts in separate fields:

SELECT 
    TO_CHAR(date '2165-04-01', 'fmDay') AS "Day",
    TO_CHAR(date '2165-04-01', 'fmDD') AS "DD",
    TO_CHAR(date '2165-04-01', 'fmMonth') AS "Month",
    TO_CHAR(date '2165-04-01', 'YYYY') AS "YYYY";

Result:

  Day   | DD | Month | YYYY 
--------+----+-------+------
 Monday | 1  | April | 2165