sql user function to return month and days in date range

Trying to write a sql function that takes in a start date and an end date and returns a table that includes  the months (numeric) included in the range along with number of days included in each month (inclusive of start and end dates)

for example if the start date is 2/27/2015 and the end date is 4/20/2015 then

I would expect

month   days

02           02
03           31
04           20
johnnyg123Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Duy PhamFreelance IT ConsultantCommented:
Try to create a table-valued UF function as below:

CREATE FUNCTION [dbo].[tbl_fn_CountMonthsAndDays](@FromDate DATETIME,	@EndDate DATETIME)
RETURNS @tblResults TABLE([Year] INT, [Month] INT, [Days] INT)
AS
BEGIN
	DECLARE	@StartDay INT, @StartMonth INT, @StartYear INT, @EndDay INT, @EndMonth INT, @EndYear INT
	SELECT	@StartDay = DAY(@FromDate), @StartMonth = MONTH(@FromDate), @StartYear = YEAR(@EndDate),
			@EndDay = DAY(@EndDate), @EndMonth = MONTH(@EndDate), @EndYear = YEAR(@EndDate)

	DECLARE	@LastDayOfMonth INT

	WHILE (@StartYear < @EndYear) OR (@StartMonth <= @EndMonth AND @StartYear = @EndYear)
	BEGIN
		-- Find last Day of current Month
		SELECT	@LastDayOfMonth = CASE
					WHEN @StartYear = @EndYear AND @StartMonth = @EndMonth THEN @EndDay
					WHEN @StartMonth IN (1, 3, 5, 7, 8, 10, 12) THEN 31
					WHEN @StartMonth IN (4, 6, 9, 11) THEN 30
					ELSE CASE
						WHEN (@StartYear % 4) = 0 THEN 29 ELSE 28
					END
				END

		-- Calculate number of Days of current Month and insert to result table
		INSERT	@tblResults
		VALUES	(
			@StartYear,
			@StartMonth,
			@LastDayOfMonth - @StartDay + 1
		)
		
		-- Next month
		SELECT	@StartYear = CASE WHEN @StartMonth = 12 THEN @StartYear + 1 ELSE @StartYear END,
				@StartMonth = CASE WHEN @StartMonth = 12 THEN 1 ELSE @StartMonth + 1 END,
				@StartDay = 1 -- Reset to 1 for next Month
	END

	-- Select result
	RETURN
END
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Use an inline-table-valued function, like below, for much better performance.  

Very sorry, but you'll need to run the SELECT statement below to see the code: I had to post it using REVERSE because of the e-e site not allowing the actual code to be submitted.  Press Ctrl-T before you run the SELECT to see the output as single lines rather than a single long string.

SELECT REVERSE('
)

)etad_dne@ ,etad_trats@ ,HTNOM(FFIDETAD DNA 0 NEEWTEB yllat.shtnom        

EREHW    

shtnom K01yllaTetc MORF    

syad SA )DNE )0 ,1 + yllat.shtnom + )etad_trats@ ,0 ,HTNOM(FFIDETAD ,HTNOM(DDAETAD ESLE                

)etad_dne@ ,1 ,YAD(DDAETAD NEHT )etad_dne@ ,etad_trats@ ,HTNOM(FFIDETAD = yllat.shtnom NEHW ESAC            

,DNE )0 ,yllat.shtnom + )etad_trats@ ,0 ,HTNOM(FFIDETAD ,HTNOM(DDAETAD ESLE                

 etad_trats@ NEHT 0 = yllat.shtnom NEHW ESAC            

 ,YAD(FFIDETAD        

,htnom SA )2 ,))2(rahcrav SA ))etad_trats@ ,yllat.shtnom ,HTNOM(DDAETAD(HTNOM(TSAC + ''0''(THGIR        

TCELES    

)    

2c 001yllaTetc NIOJ SSORC        

1c 001yllaTetc MORF        

yllat SA 1 - )yllat.1c YB REDRO(REVO )(REBMUN_WOR TCELES        

( SA K01yllaTetc    

,)    

2c 01yllaTetc NIOJ SSORC        

1c 01yllaTetc MORF        

yllat SA 1 TCELES        

( SA 001yllaTetc    

,)    

0 TCELES LLA NOINU 0 TCELES LLA NOINU 0 TCELES LLA NOINU 0 TCELES LLA NOINU 0 TCELES        

LLA NOINU 0 TCELES LLA NOINU 0 TCELES LLA NOINU 0 TCELES LLA NOINU 0 TCELES LLA NOINU yllat SA 0 TCELES        

( SA 01yllaTetc    

HTIW    

( NRUTER

SA

ELBAT SNRUTER

)

emitetad etad_dne@    

,emitetad etad_trats@    

( syaDhtiWshtnoMteG.obd NOITCNUF ETAERC
')
0
Scott PletcherSenior DBACommented:
If you have an existing tally table of sequential numbers, including zero in the table, you can do this:

CREATE FUNCTION dbo.GetMonthsWithDays (
    @start_date datetime,
    @end_date datetime
)
RETURNS TABLE
AS
RETURN (
    SELECT
        RIGHT('0' + CAST(MONTH(DATEADD(MONTH, months.tally, @start_date)) AS varchar(2)), 2) AS month,
        DATEDIFF(DAY,
            CASE WHEN months.tally = 0 THEN @start_date
                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date) + months.tally, 0) END,
            CASE WHEN months.tally = DATEDIFF(MONTH, @start_date, @end_date) THEN DATEADD(DAY, 1, @end_date)
                ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, @start_date) + months.tally + 1, 0) END) AS days
    FROM tally months
    WHERE
        months.tally BETWEEN 0 AND DATEDIFF(MONTH, @start_date, @end_date)
)
0
johnnyg123Author Commented:
Thanks for the responses!

I split the points because

I asked for a user function which Duy provided (it does indeed work)

However, I liked scott's suggestion of in line for faster performance
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.