Brian_Sutherland
asked on
Count weeks in Oracle
In SQL server, I can get the count of weeks between two dates by doing the following:
Select count(distinct datepart(wk,CALENDAR_DATE)
How can I do this in Oracle?
Select count(distinct datepart(wk,CALENDAR_DATE)
How can I do this in Oracle?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
- 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_C
--------------------------
4
--Oracle Date and Time formats
-- http://ss64.com/ora/syntax-fmt.html
-- WW Week of year 1-52
select to_char(sysdate,'WW') - to_char(sysdate-30,'WW') from dual
SQL> /
TO_CHAR(SYSDATE,'WW')-TO_C
--------------------------
4
--Oracle Date and Time formats
-- http://ss64.com/ora/syntax-fmt.html
-- WW Week of year 1-52
select (next_day(end_date, 'MONDAY')-next_day(start_d
FROM (SELECT SYSDATE - 378 start_date, SYSDATE end_date FROM dual)
/
FROM (SELECT SYSDATE - 378 start_date, SYSDATE end_date FROM dual)
/
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.
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.
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?
ASKER
It would be the count of the week number regardless if it is a partial week.
So, in the case of the range being sixty days, which is 8 4/7 weeks, you would count that as 9 weeks?
ASKER
Yes
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().
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.
ASKER