Split Date Range into Months

Split Date Range into Months


In several cases in life we encounter a date range defined by its start and end dates, and we need to split the range into months included in that range. For example, we may need to find the months included within a given date range, or in a bit more complex scenario, we may need to find the exact date range in each of the months included within a given date range. In that case if our date range is defined by starting date 2014-03-14, and ending date of 2014-05-16 then our result set should be:

From Date       To Date
2014-03-14     2014-03-31
2014-04-01     2014-04-30
2014-05-01     2014-05-16

This short article shows how we can split a single date's range (start date, end date) into the data set of months which included in that range. In the next step we will use this solution to break a set of date ranges (table) into one set of months.

Our Case Study

We got a table with date ranges records, defined by its start and end dates. Each record includes a value for the date range, for example, it can be total number of hours we work on a project. Our goal is to split the working hours into months, relative to the number of days in that month included in the date range.

For example if we work totally of 20 hours from 2014-02-26 to 2014-03-02 then our original (source) data is:

Start Time        End Time        Working Hours
2014-02-26       2014-03-02     20

We worked 3 days in February and 2 days in March. Since we have total number of 20 then we will split the 20 hours into 5 days, so each day have average time of 4 working hours, therefore our result set will be:

Start Time        End Time        Days      Average Working Time This month
2014-02-26      2014-02-28    3             4*3 = 12
2014-03-01      2014-03-02    2             4*2 = 8

Let's show this in database language. This is our original (source) DDL+DML:

-- I have a table which contains these columns - start date, end date and volumes
create table MyTbl (
      start_date datetime
    , end_date datetime
    , volumes int
insert MyTbl
select '20140310','20140310',100 union all
select '20130310','20140310',244 union all
select '20120310','20120516',222 union all
select '20140210','20140212',456 union all
select '20140210','20140309',3333
select * from MyTbl

We can see that the first record includes only 1 day of working, therefore in the result set we will get 1 record. But the second record includes a full year of working, therefore this record should split into 12 months included in the date range, and each month should get number of average working hours relative to the numbers of days in that month. The third record includes 3 months... and so on...

Solution and Explanation

In our solution we are going to base on finding each starting date and ending date of the month. In order to do this we will use this logic (You can use different logic for this):

-- get first+last day in the month
declare @SDate datetime = '20130210'
    DATEADD(mm, DATEDIFF(mm,0,@SDate), 0),
    DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(mm,0,@SDate)+1, 0))

We will start with a solution for splitting a single date range, which can be use for creating a function.

declare @SDate datetime = '20140310', @EDate datetime = '20140615'
select FirstDayOfMonth,LastDayOfMonth,DATEDIFF(DAY,FirstDayOfMonth,LastDayOfMonth)+1 [Number Of Days This month]
from (
    select top 100
        FirstDayOfMonth = CASE
            when DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(MONTH,N,@SDate)), 0) < @SDate
                then @SDate
        , LastDayOfMonth = CASE
            DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,@SDate))+1, 0)) > @EDate
                then @EDate
                DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,@SDate))+1, 0))
    from dbo.Numbers
        DATEADD(mm, DATEDIFF(mm,0,DATEADD(MONTH,N,@SDate)), 0) < @EDate
        -- without this filter you will get error
        -- "Adding a value to a 'datetime' column caused an overflow"
        and N < 1000
) T

Now let's move to a more complex solution without using a function for each record. In this solution we will use OUTER APPLY in order to work on the entire table as a SET. 

select start_date,end_date,volumes,FirstDayOfMonth,LastDayOfMonth
from MyTbl V
    select top 1000 -- without this filter you will get error: "Adding a value to a 'datetime' column caused an overflow"
         FirstDayOfMonth = CASE
            when DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date)), 0) < V.start_date
                then V.start_date
                DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date)), 0)
        , LastDayOfMonth = CASE
            DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date))+1, 0)) > V.end_date
                then V.end_date
                DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date))+1, 0))
    from dbo.Numbers
) T
where FirstDayOfMonth <= V.end_date

Next we need to use our splitting solution in order to get the Average working time for each month.

    DATEDIFF(DAY,FirstDayOfMonth,LastDayOfMonth)+1 as NumberOfDaysThisMonth,
    DATEDIFF(DAY,start_date,end_date) + 1 TotalNumberOfDays,
    (  CONVERT(float,DATEDIFF(DAY,FirstDayOfMonth,LastDayOfMonth)+1)/(DATEDIFF(DAY,start_date,end_date) + 1)  ) * volumes as volumesPerMonth
from MyTbl V
    select top 1000
         FirstDayOfMonth = CASE
            when DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date)), 0) < V.start_date
                then V.start_date
                DATEADD(MONTH, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date)), 0)
        , LastDayOfMonth = CASE
            DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date))+1, 0)) > V.end_date
                then V.end_date
                DATEADD(DAY,-1,DATEADD(mm, DATEDIFF(MONTH,0,DATEADD(MONTH,N,V.start_date))+1, 0))
    from _ArielyAccessoriesDB.dbo.Numbers
) T
where FirstDayOfMonth <= V.end_date


In this article we tried to show how we can split date ranges into a set of monthly records.


  • Numbers Table:

    • The code used numbers table dbo.Numbers
    • This is highly recommended to have a numbers table in the database, or in general read only accessories database. This table is indexed using clustered index and will give us better solution then build it on-the-fly in each query. If you don't have one, please build a number table.


* This article is based on Ronen Ariely's code posted in the blog:

Forums Questions

The topic covered in this article is quite common, and there are dozens of questions in various forums related to it.

Sort by: Published Date | Most Recent | Most Useful
  • Ya you are correct but Its your article dedicated to Wiki so that everybody can learn.Its not Wiki article Wiki is a platform to share articles.You are author if so I made it I.If you feel it WE is appropriate ,so let it be.


  • Hi Shanky_621,

    Thanks for comment :-)

    I have blogs system, where I post my blogs. Blog is something personal (as well a forum message). This is something that I "signed" on what is written there! In a WIKI, any person can edit the article. True, I am the original author, and the person who contributed this article to the community. Once i published the article here, it is not my article, but the community :-)

    Anyone can edit the article. Think about a situation which someone write something that the original author do not accept. This can't be in personal blog, but can be in WIKI. This is the Idea of WIKI :-) This is not personal publications but community, Just like A real free Open Source code which can be improved by everyone. The original author can not always control the evolution of the article/code. I will be happy if people will improve any of the WIKI's articles, which I post, but always will remember who is the original author.

    * Several articles like this one, I based on a blog/post which I wrote previously. In this case I usually mention it. Same as in Open source code, I will mention the people which took part in writing it, and specially the original author. Those comments should be always in the article! but as mentioned the content can be changed in time and hopefully improved.

    *** You are most welcome to follow my blogs, there you can find much more articles. I am usually posting a message in my personal Facebook page once I post a new blog. For example several days ago I published a blog on "C Sharp: Multi-Types Application". This blog I did not published in the WIKI system.


  • Please translate Hebrew comment in last section into English. Also, I removed reference to the DB Name as I wanted to make the code a bit more generic.

  • well done Naomi :-)

    I see that you fixed some grammar mistakes as well.

    I translated this article from an old post of mine, which was in Hebrew. Look like I forgot one sentence in Hebrew :-) I am not sure it is important sentence. It is basically explain why we have to use "TOP X" in this format of the query, or we will get an error. I will fix it :-)


Page 1 of 1 (4 items)