LAST_DAY

Returns the last day of the month in the specified date.

Behavior type

Immutable if the specified is a TIMESTAMP or DATE
Stable if the specified date is a TIMESTAMPTZ

Syntax

LAST_DAY ( date )

Parameters

date

The date to process, one of the following data types:

Calculating first day of month

SQL does not support any function that returns the first day in the month of a given date. You must use other functions to work around this limitation. For example:

=> SELECT DATE ('2022/07/04') - DAYOFMONTH ('2022/07/04') +1;
  ?column?
------------
 2022-07-01
(1 row)

=> SELECT LAST_DAY('1929/06/06') - (SELECT DAY(LAST_DAY('1929/06/06'))-1);
  ?column?
------------
 1929-06-01
(1 row)

Examples

The following example returns the last day of February as 29 because 2016 is a leap year:

=> SELECT LAST_DAY('2016-02-28 23:30 PST') "Last Day";
  Last Day
------------
 2016-02-29
(1 row)

The following example returns the last day of February in a non-leap year:

> SELECT LAST_DAY('2017/02/03') "Last";
    Last
------------
 2017-02-28
(1 row)

The following example returns the last day of March, after converting the string value to the specified DATE type:

=> SELECT LAST_DAY('2003/03/15') "Last";
    Last
------------
 2012-03-31
(1 row)