PostgreSQL: How to Return the Max of Two Dates

Often in PostgreSQL you may want to return the max of two different date values.

You can use the following basic syntax to do so:

SELECT *,
       GREATEST(date_A, date_B) AS max_date
FROM emp;

This particular example finds the max (i.e. most recent) date between the corresponding dates in the date_A and date_B columns of the table named emp.

The max date is returned in a new column named max_date.

The following example shows how to use this syntax in practice.

Example: How to Return the Max of Two Dates in PostgreSQL

Suppose that we create the following table named emp that contains information about when various employees sold two different products:

-- create table named 'emp'
CREATE TABLE emp (
  empID INT  PRIMARY KEY,
  date_A DATE,
  date_B DATE
);

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2022-04-01', '2024-05-14');
INSERT INTO emp VALUES (0002, '2024-07-01', '2023-05-14');
INSERT INTO emp VALUES (0003, '2024-08-15', '2023-07-17');
INSERT INTO emp VALUES (0004, '2023-12-01', '2024-08-01');
INSERT INTO emp VALUES (0005, '2024-11-23', '2024-12-01');

-- view all rows in table
SELECT * FROM emp;

 empid | start_date |  end_date  
-------+------------+------------
     1 | 2022-04-01 | 2024-05-14
     2 | 2024-07-01 | 2023-05-14
     3 | 2024-08-15 | 2023-07-17
     4 | 2023-12-01 | 2024-08-01
     5 | 2024-11-23 | 2024-12-01

The date_A and date_B columns represent the dates when each employee sold Product A and Product B, respectively.

We can use the following syntax to return the max (i.e. the most recent) date between these two date columns:

SELECT *,
       GREATEST(date_A, date_B) AS max_date
FROM emp;

 empid |   date_a   |   date_b   |  max_date  
-------+------------+------------+------------
     1 | 2022-04-01 | 2024-05-14 | 2024-05-14
     2 | 2024-07-01 | 2023-05-14 | 2024-07-01
     3 | 2024-08-15 | 2023-07-17 | 2024-08-15
     4 | 2023-12-01 | 2024-08-01 | 2024-08-01
     5 | 2024-11-23 | 2024-12-01 | 2024-12-01

The resulting table contains a new column named max_date that shows the max (i.e. the most recent) date between the date_A and date_B columns.

For example, we can see:

• The max date between 2022-04-01 and 2024-05-14 is 2024-05-14.
• The max date between 2024-07-01 and 2023-05-14 is 2024-07-01.
• The max date between 2024-08-15 and 2023-07-17 is 2024-08-15.

And so on.

You can use similar syntax to return the max date between two dates in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Add Days to a Date
PostgreSQL: Calculate Difference Between Dates in Days
PostgreSQL: Calculate Difference Between Dates in Months