All Forums Database
ylai20 4 posts Joined 09/09
06 Jun 2016
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!
 
 

CarlosAL 512 posts Joined 04/08
06 Jun 2016

Hi.
The classic solution:
SELECT CAST(((ADD_MONTHS(DATE <your date> ,-1)/100)*100)+1 AS DATE);
Cheers.
Carlos.

yuvaevergreen 93 posts Joined 07/09
07 Jun 2016

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)

You must sign in to leave a comment.