troubleshooting Question

How can I determine elapsed weeks on Oracle SQL?

Avatar of yerdaman
yerdaman asked on
Oracle Database
67 Comments1 Solution7433 ViewsLast Modified:
The challenge here is to establish the number of weeks that have elapsed between two specified dates. A week number can easily be returned using the IW format (seel below)

SELECT  TO_CHAR(TO_DATE('26/10/07','DD/MM/YY'),'YYYYIW') -
        TO_CHAR(TO_DATE('19/10/07','DD/MM/YY'),'YYYYIW') X FROM DUAL

The correct answer is returned which is '1' (i.e. one week number between the two dates)

However, the problem I encounter surfaces when I work on dates that span a calendar year (see below)

SELECT  TO_CHAR(TO_DATE('04/01/08','DD/MM/YY'),'YYYYIW') -
        TO_CHAR(TO_DATE('28/12/07','DD/MM/YY'),'YYYYIW') X FROM DUAL

Once again, the answer I am looking for is '1' but '49' is returned because of the calendar year.

I was tempted to simply establish the number of days that elapse between the two dates and divide by 7 but this will not work because we must remember that EVEN ONE DAY between the two dates can theoretically fall into different weeks (i.e. Any Sunday followed by the following Monday should return a different week).


I hope there is enough for you to work on with the info above but please contact me should more info be needed.



ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 67 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 67 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros