Month wise display

Question

Start Date: (mm/dd/yyyy) 05/09/2007
End Date: (mm/dd/yyyy) 08/20/2007

While giving the start date and end date using the above values, need to display the report like the following format.

Notes: Need Single query

Start Date End Date
05/09/2007 05/31/2007
06/01/2007 06/30/2007
07/01/2007 07/31/2007
08/01/2007 08/20/2007

Please help me with this query.

Answer 1

Here's the page with SQL Server datetime formats. You could use it like:

select 
    convert(varchar(32), StartDate, 101) as StartDate
,   convert(varchar(32), EndDate, 101) as EndDate
,   ...
from YourTable
where StartDate between '2007-09-05' and '2007-08-20'
or EndDate between '2007-09-05' and '2007-08-20'

To display rows that end or start in that timeframe.

If you'd like to enter dates in the "month-day-year" format, but your SQL Server is confiugred to use something else, you can use set dateformat. This allows you to change the setting for your session only. For dmy, this works like:

set dateformat 'mdy'
select cast('12/31/2009' as datetime)

Answer 2

You can try something like this using Sql Server 2005

DECLARE @StartDate DATETIME,
     @EndDate DATETIME

SELECT  @StartDate = '09 May 2009',
     @EndDate = '20 Aug 2009'

;WITH CTE AS(
     SELECT @StartDate StartDate,
       DATEADD(MM, 1, CAST('01 ' + DATENAME(mm, @StartDate) +' ' + CAST(DATEPART(yyyy, @StartDate) AS VARCHAR(4)) AS DATETIME)) - 1 EndDate 
     UNION ALL
     SELECT EndDate + 1 StartDate,
       CASE WHEN DATEADD(MM, 1, EndDate + 1) - 1 > @EndDate THEN @EndDate ELSE DATEADD(MM, 1, EndDate + 1) - 1 END EndDate
     FROM CTE
     WHERE EndDate < @EndDate
)
SELECT  *
FROM    CTE