Date LastDay

Snippets

Date LastDay()

Works with PostgreSQL

Any version

Written in

SQL

Depends on

Nothing

by Scott Bailey 'Artacus'

Often when working with dates, we either need to get the first day of the month or the last day of the month. Getting the first day is easy and can be done with date_trunc.

  SELECT date_trunc('MONTH', dtCol)::date;

But getting the last day is not so straight forward. Users coming from Oracle will recognize this one.

CREATE OR REPLACE FUNCTION last_day(date)
RETURNS date AS
$$
  SELECT (date_trunc('MONTH', $1) + INTERVAL '1 MONTH - 1 day')::date;
$$ LANGUAGE 'sql' IMMUTABLE STRICT;

Note

This snippet was borrowed from the Chronos Temporal Extensions project. Chronos is a complete toolkit for working with temporal data in Postgres and Oracle. I'll post some of the more general functions here. (Since I'm the author, I guess that's alright.)