So real case scenario, I was asked to calculate real nights in a month. Below are the sample scenarios:

• If guest checked in at 2016-04-23, checked out at 2016-05-01, actual nights for May 2016 = 0 night
• If guest checked in at 2016-04-23, checked out at 2016-05-05, actual nights for May 2016 = 4 nights
• If guest checked in at 2016-05-23, checked out at 2016-05-27, actual nights for May 2016 = 4 nights
• If guest checked in at 2016-05-27, checked out at 2016-06-07, actual nights for May 2016 = 5 nights
• If guest checked in at 2016-04-27, checked out at 2016-06-07, actual nights for May 2016 = 31 nights

Here’s how I do it:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David B
-- Created date: 2016-04-07
-- Description:	Calculating how many nights for a period of stay in a month
-- How to use:  select * from [dbo].[Fn_nightsmonth]('2015-01-25','2015-02-05',1 , 2015)
-- =============================================
CREATE FUNCTION [dbo].[Fn_nightsmonth](@StartDate datetime, @EndDate datetime, @for_month int, @for_year int)
RETURNS TABLE
AS
RETURN
Select
CASE
	WHEN (DATEPART(MONTH, @StartDate) = @for_month and DATEPART(MONTH, @EndDate) = @for_month) and ((DATEPART(YEAR, @StartDate) = @for_year or DATEPART(YEAR, @EndDate) = @for_year)) THEN 
		DATEDIFF(DAY, @StartDate,@EndDate) 	
	WHEN (@StartDate < cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (@EndDate between (cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date))) THEN
		DATEDIFF(DAY, DATEADD(MONTH, DATEDIFF(MONTH, -1, @EndDate)-1, 0),@EndDate)
	WHEN (@EndDate > cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date)) and (@StartDate between (cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) and (cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date))) THEN
		DATEDIFF(DAY, @StartDate,DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, @StartDate) + 1, 0))) + 1 
	WHEN ((DATEDIFF(DAY, @StartDate, cast(DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date)) + 1, 0)) as date)) >= 0) and (DATEDIFF(DAY, cast(CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as date), @EndDate) >= 0)) THEN
		DATEDIFF(DAY, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as datetime), DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, cast( CONVERT(varchar(4), @for_year) + '-' + CONVERT(varchar(2), @for_month) + '-01' as datetime)) + 1, 0))) + 1
	ELSE
		0
END as [DD]			

And what if you need between a range but not in a month let say weekly, then you can use below query:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		David B
-- Created date: 2016-06-07
-- Description:	Calculating how many nights overlap between 2 date ranges
-- How to use:  select * from [dbo].[Fn_dayoverlap]('2000-01-25','2000-02-05','2000-01-01','2000-01-31')
-- =============================================
CREATE FUNCTION [dbo].[Fn_dayoverlap](@StartDate1 datetime, @EndDate1 datetime, @StartDate2 datetime, @EndDate2 datetime)
RETURNS TABLE 
AS
RETURN
SELECT CASE 
	WHEN (NOT((@EndDate2 < @StartDate1) or (@StartDate2 > @EndDate1))) THEN 
		DATEDIFF(DAY, 
		CASE WHEN @StartDate1 > @StartDate2 THEN @StartDate1 ELSE @StartDate2 END,
		CASE WHEN @EndDate1 < @EndDate2 AND NOT(@EndDate1 >= @StartDate2) and (@StartDate2 > @StartDate1) THEN DATEADD(day,1,@EndDate1) 
			WHEN (@EndDate1 <= @EndDate2 AND (@EndDate1 >= @StartDate2)) or (@StartDate1 > @StartDate2 and @EndDate1 < @EndDate2) THEN @EndDate1 
			WHEN (@EndDate2 < @EndDate1 and (@StartDate1 > @StartDate2)) or (@StartDate2 > @StartDate1 and @EndDate1 > @EndDate2) or (@EndDate2 = @StartDate1) THEN DATEADD(day,1,@EndDate2) 
			WHEN @EndDate2 < @EndDate1 AND not (@EndDate2 = @StartDate1) THEN @EndDate2 END)
	ELSE
		0
END AS [DAY]