SQL Number of Days between 2 dates giving results per month

Posted on 2009-04-16
Last Modified: 2012-05-06
have two columns in a SQL table that contain dates in a date range. I need to count the days in the date range and group by year/month. If the dates in the range are in the same month or in two consecutive months I can calculate the days. If the dates span two or more months, I don't know how to group by the months in between the begin date and end date.

For example, if Begin_Date is 2004-10-01 and End_Date is 2005-01-06, how do I group by each month in the date range so the output looks something like this:

2004/10 31
2004/11 30
2004/12 31
2005/01 6
Question by:flickimp1717
    1 Comment
    LVL 8

    Accepted Solution

    Try this... You can do much more if u play around the date functions...
    declare @table table(ID int identity,DATE_S datetime,DATE_E datetime)
    insert into @table 
    select getdate()-60,getdate()+60
    union all
    select Getdate()-60,Getdate()+30
    union all 
    select Getdate(),Getdate()+1
    union all 
    select Getdate()+30,Getdate()-30
    select left(CONVERT(VARCHAR(10),DATE_S,111),7),
    right(CONVERT(VARCHAR(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATE_S)+1,0)),111),2)Days,
    right(CONVERT(VARCHAR(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATE_E)+1,0)),111),2)Days,
    DATE_S from @table

    Open in new window


    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Join & Write a Comment

    Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now