Q:

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

0

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

All Answers

need an explanation for this answer? contact us directly to get an explanation for this answer

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

need an explanation for this answer? contact us directly to get an explanation for this answer

total answers (1)

Similar questions


need a help?


find thousands of online teachers now