To get the correct week number based on ISO standard you can use this function:

--Credit to Ramakrishna Elashwarapu

CREATE FUNCTION dbo.tvfGetWeekNumberFromDate_ISO

(

@dDate DATETIME

)

RETURNS @WeekNumber TABLE

(

[Year] INT NULL

,[WeekNumber] INT NULL

)

WITH SCHEMABINDING

AS

BEGIN

DECLARE

@iWeekdayNumber INT

,@dCurrThurs DATETIME

,@YearFirstThurs DATETIME

,@iYearFirstThursWeekNum INT

,@dFirstThursYear DATETIME

,@iWeekNumber INT

SET @iWeekdayNumber = (((DATEPART(dw, @dDate) - 1) + (@@DATEFIRST - 1)) % 7) + 1

SET @dCurrThurs = DATEADD(d,(4 - @iWeekdayNumber),@dDate)

SET @YearFirstThurs = CAST(CAST(YEAR(@dCurrThurs) AS CHAR(4)) + '-01-01' AS DATETIME)

SET @iYearFirstThursWeekNum = (((DATEPART(dw, @YearFirstThurs) - 1) + (@@DATEFIRST - 1)) % 7) + 1

IF (@iYearFirstThursWeekNum in (1,2,3,4))

SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum),@YearFirstThurs)

ELSE

SET @dFirstThursYear = DATEADD(d,(4 - @iYearFirstThursWeekNum + 7),@YearFirstThurs)

SET @i[WeekNumber] = DATEDIFF(d,@dFirstThursYear,@dCurrThurs)/7+1

INSERT INTO @WeekNumber

SELECT YEAR(@dFirstThursYear),@iWeekNumber

RETURN

END

Then just do a CROSS APPLY and ROW_NUMBER() to sort:

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[mydate] DATETIME NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT '20121222 00:00:00.000' UNION ALL

SELECT '20121223 00:00:00.000' UNION ALL

SELECT '20121224 00:00:00.000' UNION ALL

SELECT '20121225 00:00:00.000' UNION ALL

SELECT '20121226 00:00:00.000' UNION ALL

SELECT '20121227 00:00:00.000' UNION ALL

SELECT '20121228 00:00:00.000' UNION ALL

SELECT '20121229 00:00:00.000' UNION ALL

SELECT '20121230 00:00:00.000' UNION ALL

SELECT '20121231 00:00:00.000' UNION ALL

SELECT '20130101 00:00:00.000' UNION ALL

SELECT '20130102 00:00:00.000' UNION ALL

SELECT '20130103 00:00:00.000' UNION ALL

SELECT '20130104 00:00:00.000' UNION ALL

SELECT '20130105 00:00:00.000' UNION ALL

SELECT '20130106 00:00:00.000' UNION ALL

SELECT '20130107 00:00:00.000' UNION ALL

SELECT '20130108 00:00:00.000' UNION ALL

SELECT '20130109 00:00:00.000'

SELECT

ROW_NUMBER() OVER (ORDER BY w.[Year],w.[WeekNumber]) AS RowNum

,CAST(w.[Year] AS CHAR(4))

+ (CASE

WHEN LEN(w.[WeekNumber]) = 1 THEN '0'+CAST(w.[WeekNumber] AS CHAR(1))

ELSE CAST(w.[WeekNumber] AS CHAR(2))

END)

AS YearWeek

,mydate AS OriginalDate

FROM

#TempTable AS tt

CROSS APPLY

dbo.tvfGetWeekNumberFromDate_ISO(tt.mydate) AS w

WHERE

tt.ID > 0

Output:

RowNumYearWeekOriginalDate

12012512012-12-22 00:00:00.000

22012512012-12-23 00:00:00.000

32012522012-12-24 00:00:00.000

42012522012-12-25 00:00:00.000

52012522012-12-26 00:00:00.000

62012522012-12-27 00:00:00.000

72012522012-12-28 00:00:00.000

82012522012-12-29 00:00:00.000

92012522012-12-30 00:00:00.000

102013012012-12-31 00:00:00.000

112013012013-01-01 00:00:00.000

122013012013-01-02 00:00:00.000

132013012013-01-03 00:00:00.000

142013012013-01-04 00:00:00.000

152013012013-01-05 00:00:00.000

162013012013-01-06 00:00:00.000

172013022013-01-07 00:00:00.000

182013022013-01-08 00:00:00.000

192013022013-01-09 00:00:00.000