Geek Answers Handbook

How to get the last day of the month in postgres?

How to find the last day of the month in postgres? I have date columns that are stored as numeric (18) in the format (YYYYMMDD) I'm trying to do this with

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

then find the last day of this date: for example:

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

but he 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?

Version for Postgres:

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

+4
source share
5 answers

Just using last_day :

select last_day(to_date(act_date,'YYYYMMDD'))

PS: Now I believe that you know that it is very important to choose the right tags for your question!

+4
source

For anyone who comes up with this question, looking for a way for Postgres to do this (without using Redshift), here is how you do it:

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

'2017-01-05' , . :

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;
+6

, numeric(18), , 20150118. , :

to_date(your_date_column::text, 'YYYYMMDD')

, :

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

, :

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

SQL Fiddle.

+2

Redshift INTERVAL '1 month'. dateadd(month, 1, date), .

: DATEADD(DAY, -1, (DATE_TRUNC('month', DATEADD(MONTH, 1, date))))

0

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

0

More articles:


All Articles