CODEWITHSUNDEEP
oracle-database
renu
renu

Reputation: 21

To count the number of weeks between two dates in oracle

I have used the below query to find the number of weeks between two dates:

select  count(week_id) 
from fw 
where week_begin_date >= '2015-01-01' 
and  week_end_date <= '2015-12-31';

Expected result should be 53 but the actual result is 51.

Kindly help on this.

Upvotes: 2

Views: 22017

Answers (4)

successfulmike
successfulmike

Reputation: 191

Posting below solution for anyone else that still needs this (and note to future self :-) )

I needed to print all the dates between current date's start of week (ISO week .i.e Monday) inclusive of starting and ending period.

We add back an extra week (+7 days), because it'll be one week short otherwise (wouldn't include the starting period). Also included the calculated fields so output for these can be observed simultaneously (but not truly needed)

with wk_range as ( select trunc(to_date(:endDate,'YYYYMMDD'),'IW') beginWeek, trunc(sysdate,'IW') endWeek from dual ) select wr.*, endWeek - beginWeek as daysBetween, (endWeek - beginWeek + 7)/7 as weeksBetween, endWeek - (level-1)*7 period_dates from wk_range wr connect by level <= ((endWeek - beginWeek +7)/7)

Upvotes: 0

user15748253
user15748253

Reputation: 31

I know this is a very old thread, but I used a modified version of this code today that I thought might be beneficial for someone else. My modification solves for the fractional week issue and the removal of the minus sign:

SELECT 
  CEIL(
       ABS(
            (  
               TO_DATE('20160101','YYYYMMDD')
             - TO_DATE('20161231','YYYYMMDD')
            ) / 7 
           )
      ) AS DT
FROM DUAL

The ABS function takes the absolute value of the result of subtracting the two dates, thereby eliminating the minus sign if it exists (I switched the order of the dates to demonstrate this). The CEIL function rounds up any fractional week to the next whole week (I changed the year to 2016 to demonstrate this - CEIL is logically equivalent to the ROUNDUP function in Excel). NOTE: We have to apply the ABS function first (inner parenthesis) because CEIL will also round up negative numbers, which would in effect round the weeks down if ABS were applied after CEIL. The result of this calculation is 53 (subtraction of the dates returns about -52.142857, ABS removes the minus sign, CEIL rounds up to 53).

I hope this ends up being useful to someone. Thanks.

Upvotes: 3

xQbert
xQbert

Reputation: 35323

Can't you just use the week of year function? subtract if needed...

 select to_char(to_date('12/31/2015','MM/DD/YYYY'),'WW') from dual;


select To_Number(to_char(to_date('12/31/2015','MM/DD/YYYY'),'WW')) - 
       To_number(to_char(to_date('01/01/2015','MM/DD/YYYY'),'WW')) +1
from dual;

We have to add +1 because weeks start at 1 not 0.

enter image description here

Now maybe you're after the ISO week format which would be IW instead of WW.

WW: Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.

IW: Week of year (1-52 or 1-53) based on the ISO standard.

Upvotes: 3

Asif Mehmood
Asif Mehmood

Reputation: 984

Did you try this:

SELECT 
  REPLACE(( 
     to_date('20151231','yyyymmdd') - to_date('20150101','yyyymmdd')
  )/7, '-', '')
FROM
  DUAL

Upvotes: 0

Related Questions