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

Database
Fan

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
Junior Supporter

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.

Enthusiast

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)