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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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)
)