Skip to Main Content
  • Questions

  • Counting specific days between to two dates

Breadcrumb

XMAS_TREE

The AskTOM team is taking a break over the holiday season, so we're not taking questions or responding to comments. Please have a happy and safe festive season and as always, thanks for being a member of the AskTOM community.

Question and Answer

Chris Saxon

Thanks for the question, ahmet.

Asked: November 12, 2018 - 7:11 am UTC

Last updated: November 12, 2018 - 11:06 am UTC

Version: none

Viewed 1000+ times

You Asked

Hi Tom,

İ have a case that i need to count specific days between two dates. For example i have a table that contains contract startdate, enddate and specific date like 15. 15 means every 15th day of months.
i need to count specific dates. for example startdate is 11.03.2018 and enddate is 19.04.2015. my result sould be 2(15.03.2018 and 15.04.2018) how can i do this calculation.

pls help.

thanks

and Chris said...

If you're looking to pull out the number of dates falling on a particular day of the month, you just need to pull this value out of the date.

There are various way to do this.

Below uses extract( day from ...):

with dates as (
  select date'2017-12-31' + level dt 
  from   dual
  connect by level <= 365
)
  select count(*) from dates
  where  extract ( day from dt ) = 15;

COUNT(*)   
        12 
  
with dates as (
  select date'2017-12-31' + level dt 
  from   dual
  connect by level <= 365
)
  select count(*) from dates
  where  extract ( day from dt ) = 15
  and    dt between date'2018-03-11' and date'2018-04-19';

COUNT(*)   
         2 
  
with dates as (
  select date'2017-12-31' + level dt 
  from   dual
  connect by level <= 365
)
  select count(*) from dates
  where  extract ( day from dt ) = 9
  and    dt between date'2018-03-11' and date'2018-04-19';

COUNT(*)   
         1


We're not taking comments currently, so please try again later if you want to add a comment.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.