SQL Server – How to Find Number of Days Since Beginning of Fiscal Month

DECLARE @d DATETIME = '20140311', @dm TINYINT = 26;

SELECT DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d)));

Result:

2014-02-26 00:00:00.000

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

Declare @DateTable1 table (Date1 date);

Insert into @DateTable1
select dateadd(day, number, @Range1Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End;

Declare @DateTable2 table (Date2 date);

Insert into @DateTable2
select dateadd(day, number, @Range2Begin)
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End;


-- Get individual dates
select dt1.Date1 as date, datename(w, dt1.date1) as weekday from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);


-- Get total days
select count(*) as TotalDays from @DateTable1 dt1
inner join @DateTable2 dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);

Without declaring table variables, following is the solution.

Declare @Range1Begin date = '10/16/2014',
        @Range1End   date = '10/28/2014',
        @Range2Begin date = '9/22/2014',
        @Range2End   date = '10/21/2014';

-- Get total days
select count(*) as TotalDays from (select dateadd(day, number, @Range1Begin) date1
from 
    (select distinct number from master.dbo.spt_values
     where name is null) n
where dateadd(day, number, @Range1Begin) <= @Range1End) dt1
inner join (select dateadd(day, number, @Range2Begin) date2
from 
    (select distinct number from master.dbo.spt_values
     where name is null ) n
where dateadd(day, number, @Range2Begin) <= @Range2End) dt2
on dt1.date1 = dt2.date2
where datepart(w, dt1.date1) not in (1,7);