PostgreSQL: How to Get the Last Day of the Week

Often in PostgreSQL you may want to get the last day of the week.

You can use the following basic syntax to do so:

SELECT (date_trunc('week', start_date) + '6 days'::interval)::date AS l_day
FROM emp;

This particular example will create a new column named l_day that contains the last day of the week for each date in the start_date column of the table named emp.

Here is how this syntax works:

• We use the date_trunc() function to truncate the date to the week, which returns the first day of the week by default.
• Then, we add six days to get the last day of the week.
• Then, we cast the result to a date.
• Lastly, we save the result to a column named l_day.

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

Example: How to Get Last Day of the Week in PostgreSQL

Suppose that we create the following table named emp that contains information about various employees at some company including their starting date and their total sales made:

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

-- add rows to the table 
INSERT INTO emp VALUES (0001, '2021-04-01', 134);
INSERT INTO emp VALUES (0002, '2022-07-01', 201);
INSERT INTO emp VALUES (0003, '2022-08-15', 155);
INSERT INTO emp VALUES (0004, '2022-09-01', 98);
INSERT INTO emp VALUES (0005, '2023-11-23', 65);
INSERT INTO emp VALUES (0006, '2024-02-20', 52);
INSERT INTO emp VALUES (0007, '2024-05-15', 50);

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

 empid | start_date | sales 
-------+------------+-------
     1 | 2021-04-01 |   134
     2 | 2022-07-01 |   201
     3 | 2022-08-15 |   155
     4 | 2022-09-01 |    98
     5 | 2023-11-23 |    65
     6 | 2024-02-20 |    52
     7 | 2024-05-15 |    50

Suppose that we would like to get the last day of the week for each date in the start_date column.

We can use the following syntax to do so:

SELECT empID,
       start_date,
       (date_trunc('week', start_date) + '6 days'::interval)::date AS l_day
FROM emp;

 empid | start_date |   l_day    
-------+------------+------------
     1 | 2021-04-01 | 2021-04-04
     2 | 2022-07-01 | 2022-07-03
     3 | 2022-08-15 | 2022-08-21
     4 | 2022-09-01 | 2022-09-04
     5 | 2023-11-23 | 2023-11-26
     6 | 2024-02-20 | 2024-02-25
     7 | 2024-05-15 | 2024-05-19

The new column named l_day contains the last day of the week for each corresponding date in the start_date column.

You can use similar syntax to get the last day of the week for a date column in your own table.

Additional Resources

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

PostgreSQL: How to Get the Last Day of the Month
PostgreSQL: How to Extract Month from Date
PostgreSQL: How to Extract Year and Month from Date
PostgreSQL: How to Calculate Difference Between Dates in Months