Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Given two dates like 20120302 and 20120605

I need to get a list of Months and the total days in those months that fall between those two dates, like so:

March 28
April 30
May   31
June  03
share|improve this question
2  
What have you tried so far? –  BryceAtNetwork23 May 31 '12 at 13:02
3  
Do you not include the first day in the range, or the last two days in the range? I count 29 days from March 2 - March 31, and 5 days from June 1 to June 5. Can you please clarify? –  Aaron Bertrand May 31 '12 at 13:48

5 Answers 5

Here's my attempt. Still think there are unanswered questions in your sample data, though.

DECLARE @s SMALLDATETIME, @e SMALLDATETIME;
SELECT  @s = '20120302',  @e = '20120605';

;WITH n(n) AS
(
  SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER 
  (ORDER BY [object_id])-1 FROM sys.all_objects
),
x(n,fd,ld) AS 
(
  SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
  FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld) 
  - CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
  - CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
  FROM x;

Results:

Month  Days
-----  ----
March    29
April    30
May      31
June      5
share|improve this answer
    
Check this value @s = '20120331' –  msi77 Jun 1 '12 at 6:07

You can use a recursive CTE to build a list of dates and group by month. This will not give you the exact output you need but it should give you an idea of what you can do.

declare @D1 date;
declare @D2 date;

set @D1 = '20120302';
set @D2 = '20120605';

with C(TheDate) as
(
  select @D1
  union all
  select dateadd(day, 1, C.TheDate)
  from C
  where C.TheDate < @D2
)
select cast(dateadd(month, datediff(month, 0, C.TheDate), 0) as date) as Month,
       count(*) as DayCount
from C
group by dateadd(month, datediff(month, 0, C.TheDate), 0)
option (maxrecursion 0);

Result:

Month      DayCount
---------- -----------
2012-03-01 30
2012-04-01 30
2012-05-01 31
2012-06-01 5
share|improve this answer
    
Mikael Eriksson, can u please suggest me how i use your query in my rdlc reportrs, Actually i want to add number of rows in my rdlc report dynamically according to the number of days till current date. –  Kamya sharma Jun 19 at 12:40
    
@Kamyasharma I made this a comment so you can ask him how to make this work from this answer. If he can't add an edit to this answer to explain that, then you will need to ask a new question as others explained to you before. –  jcolebrand Jun 19 at 17:03
    
@Kamyasharma I don't know what a rdlc report is and I don't understand what you want to do. I recommend you to ask a new question. –  Mikael Eriksson Jun 19 at 17:20

This should work too.

;with a
 as
  (
   select CAST('20120302' as DATetime) + 1 as d

   union all
    select DATEADD(day , 1,d) from a where DATEADD(day , 1,d) < CAST('20120605' as   DATetime)
  )

 select datename(month , d) as MonthName , count(1) as NUmDays from a
  group by datename(month , d),datepart(month , d) OPTION (MAXRECURSION 0);

--njds

share|improve this answer

Because I enjoy these types of questions, I will throw a solution I use often into the mix.

DECLARE
    @StartDate DATETIME,
    @EndDate DATETIME;

SELECT
    @StartDate = CONVERT(DATETIME, '2012-03-02T00:00:00.000', 126),
    @EndDate = CONVERT(DATETIME, '2012-06-05T00:00:00.000', 126);

WITH e1
(
    i
)
AS
(
    SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1
    UNION ALL SELECT 1  
),
e2
(
    i
)
AS
(
    SELECT
        1
    FROM e1 AS a

        CROSS JOIN e1 AS b
),
e4
(
    i
)
AS
(
    SELECT
        1
    FROM e2 AS a

        CROSS JOIN e2 AS b
),
RawDates
(
    RawDate
)
AS
(
    SELECT
        DATEADD
        (
            dd,
            ROW_NUMBER()
            OVER
            (
                ORDER BY
                    e.i ASC
            ) - 1,
            @StartDate
        ) AS RawDate
    FROM e4 AS e
),
FilteredDates
(
    FilteredDate
)
AS
(
    SELECT
        r.RawDate
    FROM RawDates AS r

    WHERE r.RawDate BETWEEN @StartDate AND @EndDate
)
SELECT
    CONVERT(VARCHAR(4), YEAR(f.FilteredDate)) + '-' + CONVERT(VARCHAR(2), MONTH(f.FilteredDate)) AS MonthString,
    COUNT(1) AS TotalDays
FROM FilteredDates AS f

GROUP BY
    CONVERT(VARCHAR(4), YEAR(f.FilteredDate)) + '-' + CONVERT(VARCHAR(2), MONTH(f.FilteredDate))

ORDER BY
    MonthString ASC;
share|improve this answer

When I have problems like this, I use a calendar table. With a calendar table, your question can be answered by a simple query.

select month_of_year, count(*)
from calendar
where cal_date > '2012-03-02' 
  and cal_date < '2012-06-05'
group by month_of_year
order by month_of_year;

month_of_year  cal_date
--
3              29
4              30
5              31
6              4
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.