[Oracle] How to get first and last day of week, of month in Oracle?
This statement returns the day of week, month:
SELECT d today,
TRUNC(d, 'IW') start_this_week,
TRUNC(TRUNC(d, 'IW') - 7) start_last_week,
TRUNC(TRUNC(d, 'IW') - 1) end_last_week,
TRUNC(d - 7) today_of_last_week,
TRUNC(d, 'MM') start_this_month,
add_months(TRUNC(d, 'MM'), -1) start_last_month,
TRUNC(TRUNC(d, 'MM') - 1) end_last_month,
add_months(d, -1) today_of_last_moth
FROM
(SELECT TO_DATE('14/02/2016','DD/MM/YYYY') d FROM dual)
Bonus, get first day of month from January to December (Similar, to get day from … to …):
SELECT add_months(input, (level-1))
FROM
(SELECT TRUNC(TO_DATE('01/11/2016','DD/MM/YYYY'),'Y') input FROM DUAL)
CONNECT BY add_months(input, (level-1)) <= TRUNC(TO_DATE('01/12/2016','DD/MM/YYYY'), 'MM')