hi all, i am trying to find all the month end date between two dates.
i have the following data in a table
WITH data1 AS
(
SELECT To_Date('4/30/2009','mm/dd/yyyy') dt, 'TEST' lname FROM dual UNION ALL
SELECT To_Date('5/01/2009','mm/dd/yyyy') dt, 'TEST2' lname FROM dual UNION all
SELECT To_Date('5/02/2009','mm/dd/yyyy') dt, 'TEST3' lname FROM dual UNION all
SELECT To_Date('5/03/2009','mm/dd/yyyy') dt, 'TEST4' lname FROM dual UNION all
SELECT To_Date('5/04/2009','mm/dd/yyyy') dt, 'TEST5' lname FROM dual UNION all
SELECT To_Date('5/05/2009','mm/dd/yyyy') dt, 'TEST6' lname FROM dual UNION ALL
SELECT To_Date('5/31/2009','mm/dd/yyyy') dt, 'TEST7' lname FROM dual UNION all
SELECT To_Date('6/01/2009','mm/dd/yyyy') dt, 'TEST' lname FROM dual
)
i would like to write a query that gives you all month end date between two dates
for example, if you are giving dates 4/1/9 and 5/6/9, the output should be
DT LNAME
-------------------
4/30/2009 TEST
IF i am giving dates 4/1/09 AND 6/1/09, THEN output should be
DT LNAME
-------------------
4/30/2009 TEST
5/31/2009 TEST7
as you can see, i want to output all month end dates between the two dates giving.
so if i give you 5/6/09 and 5/6/09, then there is no output because there is no month end between these two dates.
similarly, if i give you 4/30/09 and 4/30/09 then output should be 4/30/09 because it is a month end date
anybody can help write a query. thanks a lot