SEL ADD_MONTHS(CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE),1);
SEL ADD_MONTHS((CURRENT_DATE-EXTRACT(DAY FROM CURRENT_DATE)+1),1)-1;
The first query above does not return correct results when the previous month is shorter than the current month.
For example, April has 30 days and May has 31. If you run it in May, the first query returns the 30th of May instead of the 31st.
The second query above is good.
CAST('2014-05-15' AS DATE) AS The_Date,
ADD_MONTHS(The_Date-EXTRACT(DAY FROM The_Date),1) AS EOM_v1,
ADD_MONTHS((The_Date-EXTRACT(DAY FROM The_Date)+1),1)-1 AS EOM_v2,
ADD_MONTHS(The_Date, 1) - EXTRACT(DAY FROM ADD_MONTHS(The_Date, 1)) AS EOM_v3
Version 2 and 3 work correctly. Don't use Version 1.
I like Version 2, it's more succinct. Version 3 also works and is arguably easier to understand at first glance.