CODEWITHSUNDEEP
sqloracle-databaseselectdate
ssahu
ssahu

Reputation: 888

Number of fridays between two dates

How do I find the number of fridays between two dates(including both the dates) using a select statement in oracle sql?

Upvotes: 8

Views: 10706

Answers (7)

onedaywhen
onedaywhen

Reputation: 57023

See:

Why should I consider using an auxiliary calendar table?

The article's code is specifically for SQL Server but the techniques are portable to most SQL platforms.

With a Calendar table in place your query could be as simple as

SELECT COUNT(*) AS friday_tally
  FROM YourTable AS T1
       INNER JOIN Calendar AS C1
          ON C1.dt BETWEEN T1.start_date AND T1.end_date
 WHERE C1.day_name = 'Friday'; -- could be a numeric code

Upvotes: 2

user1672287
user1672287

Reputation: 23

select ((DATEDIFF(dd,@a,@b)) + DATEPART(dw,(@a-6)))/7

Upvotes: -1

Ram
Ram

Reputation: 1

SELECT (NEXT_DAY('31-MAY-2012','SUN')
-NEXT_DAY('04-MAR-2012','SUN'))/7 FROM DUAL

Upvotes: -1

Tony Andrews
Tony Andrews

Reputation: 132570

This will do it:

select ((next_day(date2-7,'FRI')-next_day(date-1,'FRI'))/7)+1 as num_fridays
from data

Perhaps best if I break that down. The NEXT_DAY function returns the next day that is a (Friday in this case) after the date.

So to find the first Friday after d1 would be:

next_day( d1, 'FRI')

But if d1 is a Friday that would return the following Friday, so we adjust:

next_day( d1-1, 'FRI')

Similarly to find the last Friday up to and including d2 we do:

next_day( d1-7, 'FRI')

Subtracting the 2 gives a number of days: 0 if they are the same date, 7 if they a re a week apart and so on:

next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')

Convert to weeks:

(next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7

Finally, if they are the same date we get 0, but really there is 1 Friday, and so on so we add one:

((next_day( d1-7, 'FRI') - next_day( d1-1, 'FRI')) / 7) + 1

Upvotes: 12

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95522

I have to throw in my two cents for using a calendar table. (It's a compulsion.)

select count(*) as num_fridays
from calendar
where day_of_week = 'Fri'
  and cal_date between '2011-01-01' and '2011-02-17';

num_fridays
-----------
6

Dead simple to understand. Takes advantage of indexes.

Maybe I should start a 12-step group. Calendar Table Anonymous.

Upvotes: 6

immutabl
immutabl

Reputation: 6903

Try modifying this one:

CREATE OR REPLACE FUNCTION F_WORKINGS_DAYS  
(V_START_DATE IN DATE, V_END_DATE IN DATE)

RETURN NUMBER IS
DAY_COUNT NUMBER := 0;
CURR_DATE DATE;

BEGIN -- loop through and update
CURR_DATE := V_START_DATE;
WHILE CURR_DATE <= V_END_DATE
LOOP
    IF TO_CHAR(CURR_DATE,'DY') NOT IN ('SAT','SUN') -- Change this bit to ignore all but Fridays
        THEN DAY_COUNT := DAY_COUNT + 1;
    END IF;
    CURR_DATE := CURR_DATE + 1;
END LOOP;

    RETURN DAY_COUNT;
END F_WORKINGS_DAYS;

/

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55489

select sum(case when trim(to_char(to_date('2009-01-01','YYYY-MM-DD')+rownum,'Day')) = 'Friday' then 1 else 0 end) number_of_fridays
from dual
connect by level <= to_date('&end_date','YYYY-MM-DD') - to_date('&start_date','YYYY-MM-DD')+1;

Original source - http://forums.oracle.com/forums/thread.jspa?messageID=3987357&tstart=0

Upvotes: 1

Related Questions