CODEWITHSUNDEEP
postgresql
user3756488
user3756488

Reputation: 233

last_day in PostgreSQL

The oracle last_day function return the last day of the month. example:

nls_date_format='YYYY-MM-DD H24:MI:SS'
select last_day(sysdate) from dual;
LAST_DAY(SYSDATE)
-------------------
2014-06-30 15:45:43

oracle return the time value as well.

I have tried below sql in PostgreSQL which return the last day of month but time value is "00:00:00". 

select (date_trunc('month', now()) + interval '1 month -1 day')::timestamp(0);
         ?column?          
---------------------------
 2014-06-30 00:00:00
(1 row)

the sql return the date correctly but I want date and time like oracle.

Upvotes: 1

Views: 4564

Answers (2)

sibert
sibert

Reputation: 2228

The function last_day() should do the trick. This function should be found in a extenstion package called "orafce". Containing other functions used in Oracle.

  1. Install the correct version of orafce package (if needed):

    apt-get install postgresql-9.1-orafce

  2. SQL:

    Create Extension orafce

  3. If error rename orafce--3.0.sql to orafce--3.03.sql or user later version of orafce.

  4. Use the function:

    SELECT last_day('2015-01-01')

Works like expected and the result is '2015-01-31'

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

select (
    date_trunc('month', now())
    + interval '1 month -1 day'
    + now()::time
)::timestamp(0);

Upvotes: 4

Related Questions