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)
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.
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!
The classic solution:
SELECT CAST(((ADD_MONTHS(DATE <your date> ,-1)/100)*100)+1 AS DATE);