How to get first date of last month based on max date from another table?

Database
N/A

How to get first date of last month based on max date from another table?

Hi all,

I have a query to get the first date of last month

SELECT ADD_MONTHS(DATE'2016-04-01' - EXTRACT(DAY FROM DATE'2016-04-01') + 1,-1)  

>>'2016-03-01'

This is works if I hardcode the date or I use current date, but it will has problem if I want to run previous month. Now I am thinking to replace the date with a max date that from another table, which is actually just a simple table to keep the date that I want to run.

Example:

RPT_MTH

=======

01-01-2016

01-02-2016

01-03-2016

01-03-2016

I have tried to modify the script like below, but not working..

SELECT ADD_MONTHS(SELECT MAX(RPT_MTH) FROM db.RPT_MTH - EXTRACT(DAY FROM SELECT MAX(RPT_MTH) FROM DB.RPT_MTH) + 1,-1)

Anyone can help? Thanks!

2 REPLIES

Re: How to get first date of last month based on max date from another table?

Hi.

The classic solution:

SELECT CAST(((ADD_MONTHS(DATE <your date> ,-1)/100)*100)+1 AS DATE);

Cheers.

Carlos.

Re: How to get first date of last month based on max date from another table?

Try with brackets..
SELECT ADD_MONTHS((SELECT MAX(RPT_MTH) FROM db.RPT_MTH) - EXTRACT(DAY FROM (SELECT MAX(RPT_MTH) FROM DB.RPT_MTH)) + 1,-1)