select right(convert(varchar(33),'2012-08-31 00:00:00.000',120),12) as col1
/*
00:00:00.000
*/

------解决方案--------------------
探讨

对不起可能我写的不太明白是这样的
列名：time
2012-08-31 00:00:00.000
2012-07-23 23:59:59.000
2012-08-31 23:59:59.000
2012-06-15 00:00:00.000
我要得到
2012-08-31 00:00:00.000
2012-06-15 00:00:00.000


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

select convert(varchar(30),cast(CAST(time字段 as DATE) as datetime),121)

------解决方案--------------------
SQL code

select cast(convert(varchar(10),time as datetime)

------解决方案--------------------
探讨


SQL code

select right(convert(varchar(33),'2012-08-31 00:00:00.000',120),12) as col1
/*
00:00:00.000
*/