Requirement: I want to have an Oracle SQL query where I will pass a random Date value, and as a result it should return me the Date that we have passed, Week Number for that Date, the Week Start Date, and the Week End Date.

Sample example is shown below.

Solution: For solution of the above requirement we will use the below query.

WITH TEMP_TABLE AS
(
SELECT TO_DATE(&P1,'DD-MON-YYYY') AS DATE_VALUE FROM DUAL
)
SELECT DATE_VALUE "DATE",
TO_CHAR(TO_DATE(DATE_VALUE,'DD-MON-YYYY'),'WW')+1 "WEEK NUMBER",
TO_CHAR(TRUNC(TO_DATE(DATE_VALUE,'DD-MON-YYYY'), 'IW')-1,'DD-MON-YYYY') "WEEK START DATE",
TO_CHAR(NEXT_DAY(TRUNC(TO_DATE(DATE_VALUE,'DD-MON-YYYY'),'IW'),'SATURDAY'),'DD-MON-YYYY') "WEEK END DATE"
FROM TEMP_TABLE;

The result of the above query is as shown below

Hence, the solution to our requirement.

If you like the post please comment, share, and do join me on Facebook. Please subscribe to my YouTube Channel for video tutorials.

Thanks & Regards,
Susanto Paul

 3,026 total views,  3 views today