CODEWITHSUNDEEP
sqloracle-database
Xophmeister
Xophmeister

Reputation: 9211

Number of particular days between two dates

Is there an "easy" way to calculate the count of a particular day between two dates (e.g., say I wanted to know the number of Tuesdays between 1st January, 2000 and today)? Moreover, the same question applies more broadly to different units (e.g., how many 2pms between two dates, how many Februaries, how many 21st Augusts, etc.)... The best I've come up with (for days between dates) is this:

with calendar as (
  select     to_char(:start + level - 1, 'DAY') dayOfWeek
  from       dual
  connect by level <= ceil(:end - :start)
)
select   dayOfWeek, count(dayOfWeek)
from     calendar
group by dayOfWeek;

I would have to create a view of this -- hardcoding the start and end dates -- to make it convenient(ish) to use; either that or write a function to do the dirty work. That wouldn't be difficult, but I'm wondering if there's already an Oracle function that could do this, accounting for things like leap days, etc.

EDIT This popped up in the related links when I posted this. That more-or-less answers the question for days and I know there's a months_between function that I could use for particular months. Any other related functions I should know about?

Upvotes: 2

Views: 626

Answers (1)

Art
Art

Reputation: 5782

Replace start/end dates with your dates. This query calc number of TUE from Jan 1-2013 till today, which is 18:

SELECT count(*) number_of_tue
  FROM
 (
 SELECT TRUNC(TO_DATE(Sysdate), 'YEAR') + LEVEL-1 start_dt
      , To_Char(TRUNC(TO_DATE(Sysdate), 'YEAR') + LEVEL - 1, 'DY') wk_day
      , To_Char(TRUNC(TO_DATE(Sysdate), 'YEAR') + LEVEL - 1, 'D')  wk_day#
      , trunc(Sysdate) end_dt
  FROM DUAL
 CONNECT BY LEVEL <= trunc(Sysdate) - trunc(Sysdate, 'YEAR') -- replace with your start/end dates
 )
 WHERE wk_day# = 3 -- OR wk_day = 'TUE' -- 
/

Upvotes: 1

Related Questions