CODEWITHSUNDEEP
sqlamazon-redshift
Hare Rama Hare Krishna
Hare Rama Hare Krishna

Reputation: 1115

How to get the last day of month in postgres?

How to find the last day os the month in postgres? I have a date columns stored as numeric(18) in the format(YYYYMMDD) I am trying it to make it date using 

to_date("act_dt",'YYYYMMDD') AS "act date"

then find the last day of this date: like this:

(select (date_trunc('MONTH',to_date("act_dt",'YYYYMMDD')) + INTERVAL '1 MONTH - 1 day')::date)

but it gives me this error:

ERROR: Interval values with month or year parts are not supported
  Detail: 
  -----------------------------------------------
  error:  Interval values with month or year parts are not supported
  code:      8001
  context:   interval months: "1"
  query:     673376
  location:  cg_constmanager.cpp:145
  process:   padbmaster [pid=20937]
  -----------------------------------------------

Any help?

Postgres version:

PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.874

Upvotes: 57

Views: 145437

Answers (7)

vinod
vinod

Reputation: 11

Try this:

select date( date_trunc ('month', (current_date + INTERVAL '1 Month '))) -1 ;

hope this help full

Upvotes: 1

wspurgin
wspurgin

Reputation: 2733

For anybody coming to this question looking for the Postgres way to do this (not using Redshift), here's how you'd do it:

SELECT (date_trunc('month', '2017-01-05'::date) + interval '1 month' - interval '1 day')::date
AS end_of_month;

Replacing the '2017-01-05' with whatever date you want to use. You can make this into a function like this:

create function end_of_month(date)
returns date as
$$
select (date_trunc('month', $1) + interval '1 month' - interval '1 day')::date;
$$ language 'sql'
immutable strict;

EDIT Postgres 11+

Pulling this out of the comments from @Gabriel, you can now combine interval expressions in one interval (which makes things a little shorter):

select (date_trunc('month', now()) + interval '1 month - 1 day')::date as end_of_month;

-- +--------------+
-- | end_of_month |
-- +--------------+
-- | 2021-11-30   |
-- +--------------+
-- (1 row)

Upvotes: 145

Houari
Houari

Reputation: 5631

If you're using Amazon AWS Redshift then you can use Redshift's LAST_DAY function. While Redshift is based on PostgreSQL, the LAST_DAY function is not available in PostgreSQL, for a solution for PostgreSQL see @wspurgin's answer.

https://docs.aws.amazon.com/redshift/latest/dg/r_LAST_DAY.html

LAST_DAY( { date | timestamp } )

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE, regardless of the data type of the date argument.

For example:

SELECT LAST_DAY( TO_DATE( act_date, 'YYYYMMDD' ) )

Upvotes: 7

Christopher Terry
Christopher Terry

Reputation: 101

date_trunc('month',current_date) + interval '1 month' - interval '1 day'

Truncating any date or timestamp to the month level will give you the first of the month containing that date. Adding a month gives you the first of the following month. Then, removing a day will give you the date of the last day of the month of the provided date.

Upvotes: 9

Rakesh H B
Rakesh H B

Reputation: 1

select to_char(date_trunc('month', now() + '01 Months'::interval) - '01 Days'::interval, 'YYYYmmDD'::text)::numeric as end_period_n

Upvotes: 0

Daniel Evanko
Daniel Evanko

Reputation: 9

For future searches, Redshift does not accept INTERVAL '1 month'. Instead use dateadd(month, 1, date) as documented here.

To get the end of the month use: DATEADD(DAY, -1, (DATE_TRUNC('month', DATEADD(MONTH, 1, date))))

Upvotes: 0

Andomar
Andomar

Reputation: 238086

Okay, so you've got a numeric(18) column containing numbers like 20150118. You can convert that to a date like:

to_date(your_date_column::text, 'YYYYMMDD')

From a date, you can grab the last day of the month like:

(date_trunc('month', your_date_column) + 
    interval '1 month' - interval '1 day')::date;

Combined, you'd get:

select  (date_trunc('month', to_date(act_dt::text, 'YYYYMMDD')) + 
           interval '1 month' - interval '1 day')::date
from    YourTable;

Example at SQL Fiddle.

Upvotes: 6

Related Questions