Count weeks in Oracle

Brian_Sutherland
Brian_Sutherland used Ask the Experts™
on
In SQL server, I can get the count of weeks between two dates by doing the following:


Select count(distinct datepart(wk,CALENDAR_DATE)) from dbo.TIME_DIM where CALENDAR_DATE between '01/01/2012' and '03/31/2012'

How can I do this in Oracle?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2011
Commented:
- try the following:

SELECT floor((to_date('03/31/2012','mm/dd/yyyy') - to_date('01/01/2012','mm/dd/yyyy')) / 7) NoOfWeek FROM DUAL;

Open in new window

Author

Commented:
Oracle has no better functions to do this?
Top Expert 2011

Commented:
- not that i know. you can wait for other experts to response on this.
or


select to_char(sysdate,'WW') -  to_char(sysdate-30,'WW')   from dual
SQL> /

TO_CHAR(SYSDATE,'WW')-TO_CHAR(SYSDATE-30,'WW')
----------------------------------------------
                                             4


--Oracle Date and Time formats
-- http://ss64.com/ora/syntax-fmt.html
-- WW    Week of year 1-52
Muhammad Ahmad ImranDatabase Developer

Commented:
select (next_day(end_date, 'MONDAY')-next_day(start_date, 'MONDAY'))/7 diff
FROM (SELECT SYSDATE - 378 start_date, SYSDATE end_date FROM dual)
/
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
tangchunfeng,

Works great until the dates span more than one year.

leoahmad,

I apologize if I am wrong but it appears that you copied your post from:
https://forums.oracle.com/forums/thread.jspa?threadID=472750

If you copy form an Internet source, you need to post the link.

Brian_Sutherland,

Check the link I posted.  There are several ways in there.
awking00Information Technology Specialist

Commented:
What do you consider to be the number of weeks? The number of whole weeks or, if say the range is 60 days, the fractional portion of the week as well?

Author

Commented:
It would be the count of the week number regardless if it is a partial week.
awking00Information Technology Specialist

Commented:
So, in the case of the range being sixty days, which is 8 4/7 weeks, you would count that as 9 weeks?

Author

Commented:
Yes
awking00Information Technology Specialist

Commented:
If that's the case, tangchunfeng's won't do because of the possible difference in the years of the range (as slightwv stated), leoahmad's won't accommodate the partial week, but OP_Zaharin's will work if you change the floor() function to ceil().
awking00Information Technology Specialist

Commented:
Since OP_Zaharin's example represents 90 days (i.e. 12 weeks and 6 days), I assume that should be represented as 13 weeks, in which case I also assume you used ceil instead of floor to get your result.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial