How can I count and group by between two date?

For example, the start date = '20100530' and the end date = '20100602'

How can I write a SQL to display the below result?

month: may, 2开发者_运维百科 days
month: June, 2 days

Use a recursive CTE to generate all of the dates between the start and end, and then do a simple group and count (caution, not tested, but should be close if not exactly right):

with dates (the_date) as (
  select @start_date 
  UNION ALL
  select dateadd(dd, 1, the_date) from dates where the_date <= @end_date
)
  select
    datepart(mm, the_date) month,
    count(*) num_days
  from
    dates
  group by
    datepart(mm, the_date)

TBH, you really need to provide more schema and information about the source data. However, given what little we know, you should be able to write:

Select DateName('month', [Date]) As Month
    , Cast(DateDiff(d, @StartDate, @EndDate) As varchar(10) - 1) + ' days' 
From Table
Where [Date] Between @StartDate And @EndDate

What we'd need to know to refine our solutions is exactly how 2 days is supposed to be calculated. Is it the days between the start and end date? Is it the day of the second parameter?