How to sort varchar string properly with numeric values on both ends?

Question

I'm building a Common Table Expression (CTE) in SQL Server 2008 to use in a PIVOT query.

I'm having difficulty sorting the output properly because there are numeric values that sandwich the string data in the middle. Is it possible to do this?

This is a quick and dirty example, the real query will span several years worth of values.

Example:

Declare @startdate as varchar(max);
Declare @enddate as varchar(max);
Set @startdate = cast((DATEPART(yyyy, GetDate())-1) as varchar(4))+'-12-01';
Set @enddate = cast((DATEPART(yyyy, GetDate())) as varchar(4))+'-03-15';

WITH DateRange(dt) AS
    (
        SELECT CONVERT(datetime, @startdate) dt
        UNION ALL
        SELECT DATEADD(dd,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT DISTINCT ',' + QUOTENAME((cast(DATEPART(yyyy, dt) as varchar(4)))+'-Week'+(cast(DATEPART(ww, dt) as varchar(2)))) FROM DateRange

Current Output:

,[2012-Week48]
,[2012-Week49]
,[2012-Week50]
,[2012-Week51]
,[2012-Week52]
,[2012-Week53]
,[2013-Week1]
,[2013-Week10]
,[2013-Week11]
,[2013-Week2]
,[2013-Week3]
,[2013-Week4]
,[2013-Week5]
,[2013-Week6]
,[2013-Week7]
,[2013-Week8]
,[2013-Week9]

Desired Output:

,[2012-Week48]
,[2012-Week49]
,[2012-Week50]
,[2012-Week51]
,[2012-Week52]
,[2012-Week53]
,[2013-Week1]
,[2013-Week2]
,[2013-Week3]
,[2013-Week4]
,[2013-Week5]
,[2013-Week6]
,[2013-Week7]
,[2013-Week8]
,[2013-Week9]
,[2013-Week10]
,[2013-Week11]

EDIT

Of course after I post the question my brain started working. I changed the DATEADD to add 1 week instead of 1 day and then took out the DISTINCT in the select and it worked.

DECLARE @startdate AS VARCHAR(MAX);
DECLARE @enddate AS VARCHAR(MAX);
SET @startdate = CAST((DATEPART(yyyy, GetDate())-1) AS VARCHAR(4))+'-12-01';
SET @enddate = CAST((DATEPART(yyyy, GetDate())) AS VARCHAR(4))+'-03-15';

WITH DateRange(dt) AS
    (
            SELECT CONVERT(datetime, @startdate) dt
            UNION ALL
            SELECT DATEADD(ww,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT ',' + QUOTENAME((CAST(DATEPART(yyyy, dt) AS VARCHAR(4)))+'-Week'+(CAST(DATEPART(ww, dt) AS VARCHAR(2)))) FROM DateRange

Answer 1

I needed to change the DATEADD portion of the query and remove the DISTINCT. Once changed the order sorted properly on it's own

DECLARE @startdate AS VARCHAR(MAX);
DECLARE @enddate AS VARCHAR(MAX);
SET @startdate = CAST((DATEPART(yyyy, GetDate())-1) AS VARCHAR(4))+'-12-01';
SET @enddate = CAST((DATEPART(yyyy, GetDate())) AS VARCHAR(4))+'-03-15';

WITH DateRange(dt) AS
    (
            SELECT CONVERT(datetime, @startdate) dt
            UNION ALL
            SELECT DATEADD(ww,1,dt) dt FROM DateRange WHERE dt < CONVERT(datetime, @enddate)
    )
    SELECT ',' + QUOTENAME((CAST(DATEPART(yyyy, dt) AS VARCHAR(4)))+'-Week'+(CAST(DATEPART(ww, dt) AS VARCHAR(2)))) FROM DateRange