CODEWITHSUNDEEP
sqlsql-server
Wael Arbi
Wael Arbi

Reputation: 27

Getting number of days for a specific month and year between two dates SQL

I want to retrieve the number of days between two dates that overlap a specific month.

For Example :

Month = 1 
Year = 2020 
StartDate ='2019-11-12' 
ENDDate ='2020-1-13' 

Result = 13 days

13 because there are 13 days between the dates that are in the selected month: January 2020

Other Example:

Month=9 
Year =2019
StartDate = '2019-8-13' 
ENDDate ='2020-1-1' 

Result = 30 days

30 because there are 30 days between the dates that are in the selected month: September 2019

Upvotes: 0

Views: 1195

Answers (1)

Nick
Nick

Reputation: 147166

The generic formula for the number of overlapping days in two ranges is

MAX(MIN(end1, end2) - MAX(start1, start2) + 1, 0)

In your case you have one set of Start and End dates, you must construct the other from the given month and year using datefromparts and eomonth.

Unfortunately SQL Server doesn't support LEAST and GREATEST formulas as do MySQL and Oracle, so this is a bit painful to implement. Here's an example using variables:

declare @month int;
declare @year int;
declare @startDate date;
declare @endDate date;
declare @startOfMonth date;
declare @endOfMonth date;
declare @minEnd date;
declare @maxStart date;
set @month = 1;
set @year = 2020;
set @startDate = '2019-11-12';
set @endDate = '2020-01-13';
set @startOfMonth = datefromparts(@year, @month, 1)
set @endOfMonth = eomonth(@startOfMonth)
set @minEnd = case when @endDate < @endOfMonth then @endDate
                   else @endOfMonth
              end;
set @maxStart = case when @startDate < @startOfMonth then @startOfMonth
                     else @startDate
                end;
select case when datediff(day, @maxStart, @minEnd) + 1 < 0 then 0
            else datediff(day, @maxStart, @minEnd) + 1
       end as days_in_month

Output:

13

Demo on dbfiddle; this includes other sample date ranges.

You could implement something similar using a series of CTEs if the values are derived from a table.

Upvotes: 1

Related Questions