Oracle: Start date and end date of each month in current year from current month

Oracle Datetime: Exercise-8 with Solution

Write a Oracle SQL statement to get the start date and end date of each month in current year from current month.

Sample Solution:

Oracle Code:

SELECT ADD_MONTHS (TRUNC (SYSDATE, 'MONTH'), i) start_date,
TRUNC (LAST_DAY (ADD_MONTHS (SYSDATE, i))) end_date 
FROM XMLTABLE ('for $i in 0 to xs:int(D) 
               return $i'PASSING XMLELEMENT (d,FLOOR (MONTHS_BETWEEN (ADD_MONTHS (TRUNC (SYSDATE, 'YEAR') - 1, 12),  SYSDATE)))COLUMNS i INTEGER PATH '.');  

Output:

  	START_DATE	END_DATE
1	2/1/2021 12:00:00 AM	2/28/2021 12:00:00 AM
2	3/1/2021 12:00:00 AM	3/31/2021 12:00:00 AM
3	4/1/2021 12:00:00 AM	4/30/2021 12:00:00 AM
4	5/1/2021 12:00:00 AM	5/31/2021 12:00:00 AM
5	6/1/2021 12:00:00 AM	6/30/2021 12:00:00 AM
6	7/1/2021 12:00:00 AM	7/31/2021 12:00:00 AM
7	8/1/2021 12:00:00 AM	8/31/2021 12:00:00 AM
8	9/1/2021 12:00:00 AM	9/30/2021 12:00:00 AM
9	10/1/2021 12:00:00 AM	10/31/2021 12:00:00 AM
10	11/1/2021 12:00:00 AM	11/30/2021 12:00:00 AM
11	12/1/2021 12:00:00 AM	12/31/2021 12:00:00 AM

Improve this sample solution and post your code through Disqus.

Previous: Write a Oracle SQL statement to get number of days between two given dates.