Sql Query create date range with single date column

I have a table containing a single date column say special date (stored as yyyymmdd )

How do i create a date range among the small subset of rows?

Example table contains a date column with following values 01-jan-2010, 01-feb-2010, 01-mar-2010开发者_Go百科

I need

01-jan-2010 - 01-feb-2010
01-feb-2010 - 01-mar-2010

.... ....

Please help.

You can try something like

DECLARE @Table TABLE(
        DateVal DATETIME
)

INSERT INTO @Table SELECT '01 Jan 2010'
INSERT INTO @Table SELECT '01 Feb 2010'
INSERT INTO @Table SELECT '01 Mar 2010'

;WITH DateVals AS (
    SELECT  *,
            ROW_NUMBER() OVER(ORDER BY DateVal) RowID
    FROM    @Table
)
SELECT  s.DateVal StartDate,
        e.DateVal EndDate
FROM    DateVals s INNER JOIN
        DateVals e ON   s.RowID + 1 = e.RowID

Output

StartDate                  EndDate
2010-01-01 00:00:00.000  2010-02-01 00:00:00.000
2010-02-01 00:00:00.000  2010-03-01 00:00:00.000

You can avoid the CTE by using

SELECT  s.DateVal StartDate,
        MIN(e.DateVal) EndDate
FROM    @Table s LEFT JOIN
        @Table e    ON  s.DateVal < e.DateVal
GROUP BY    s.DateVal
HAVING  MIN(e.DateVal) IS NOT NULL

But I do not see why you wish to do so.