Sat. Jan 16th, 2021

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

 151 total views,  1 views today

By Susanto Paul

Susanto is an Oracle ACE, a qualified MCA, MBA, and a highly-skilled Senior Oracle Specialist. He is an enthusiastic Blogger and YouTuber who helps learners to solve their complex problems more efficiently. He has 9+ years of experience in multiple technologies like AWS, Oracle ADF, Oracle APEX, Oracle JET, Oracle VBCS, Oracle IDCS, Oracle PL/SQL, Oracle Integration Cloud, Java, JDBC, Servlets, JSP, Spring, Hibernate, HTML5, CSS3, JavaScript, TypeScript, NodesJS, Angular, MySQL, Oracle WebLogic Server, JUnit, JMeter, Selenium Web Driver, etc. He is a certified: Oracle Certified Professional Java SE 6 Programmer, Oracle ADF 11g Certified Implementation Specialist, Oracle Cloud Platform Application Integration 2020 Certified Specialist, Oracle Cloud Infrastructure Foundations 2020 Certified Associate, and Oracle Cloud Infrastructure Developer 2020 Certified Associate

Leave a Reply

Your email address will not be published. Required fields are marked *