--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([date] DATETIME)
INSERT [tb]
SELECT '2012-10-14' UNION ALL
SELECT '2012-10-12' UNION ALL
SELECT '2012-10-13' UNION ALL
SELECT '2012-10-11' UNION ALL
SELECT '2012-10-9' UNION ALL
SELECT '2012-10-8' UNION ALL
SELECT '2012-10-5' UNION ALL
SELECT '2012-10-3' UNION ALL
SELECT '2012-10-4' UNION ALL
SELECT '2012-10-1'
--------------开始查询--------------------------

SELECT [date] 
FROM [tb] ORDER BY CASE WHEN DATEDIFF(dd,GETDATE(),[date])>=0 THEN 0 ELSE 1 END ,DATEDIFF(dd,GETDATE(),[date])  
----------------结果----------------------------
/* 
date
2012-10-11 00:00:00.000
2012-10-12 00:00:00.000
2012-10-13 00:00:00.000
2012-10-14 00:00:00.000
2012-10-01 00:00:00.000
2012-10-03 00:00:00.000
2012-10-04 00:00:00.000
2012-10-05 00:00:00.000
2012-10-08 00:00:00.000
2012-10-09 00:00:00.000
*/

------解决方案--------------------

SQL code
好吧 爱姐 你赢了

drop table if exists test1;

CREATE TABLE TEST1
(
ID INT,
ENDTIME DATETIME
);
INSERT INTO TEST1 VALUES(1,'2012-10-07');
INSERT INTO TEST1 VALUES(2,'2012-10-08');
INSERT INTO TEST1 VALUES(3,'2012-10-09');
INSERT INTO TEST1 VALUES(4,'2012-10-06');
INSERT INTO TEST1 VALUES(5,'2012-10-12');
INSERT INTO TEST1 VALUES(6,'2012-10-13');

SELECT * FROM test1 ORDER BY CASE WHEN DATEDIFF(ENDTIME,now())>0 then 0 else 1 end ,endtime;

/*
5    2012-10-12 00:00:00
6    2012-10-13 00:00:00
4    2012-10-06 00:00:00
1    2012-10-07 00:00:00
2    2012-10-08 00:00:00
3    2012-10-09 00:00:00

*/