sql查询时间

[okh_g]

列1：2012-08-31 00:00:00.000
请问我如何写查询列1时间后几位为 00:00:00.000 的语句

[SQL77]

SELECT STUFF(CONVERT(VARCHAR(19),COL1,120),1,11,'')

[开着拖拉机泡妞]

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

[okh_g]

对不起可能我写的不太明白是这样的
列名：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

[SQL77]

[Quote=引用 3 楼 的回复:]
对不起可能我写的不太明白是这样的
列名：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
[/Quote]
CONVERT(DATETIME,CONVERT(VARCHAR(10),TIME,120))

[sql2015]

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

[问苍天人生几何]

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

[雪祭2012]

[Quote=引用 2 楼 的回复:]

SQL code

select right(convert(varchar(33),'2012-08-31 00:00:00.000',120),12) as col1
/*
00:00:00.000
*/
[/Quote]
修改如下：

DECLARE @x TABLE (TIME NVARCHAR(30))
INSERT INTO @x
( TIME )
SELECT '2012-08-30 00:00:00.000' UNION ALL
SELECT '2012-08-25 00:00:00.000' UNION ALL
SELECT '2012-08-31 10:00:00.000' UNION ALL
SELECT '2012-08-31 05:00:00.000' UNION ALL
SELECT '2012-08-31 14:00:00.000'
--SELECT * FROM @x
SELECT time FROM (SELECT time,right(convert(varchar(33),time,120),12) AS w FROM @x) x
WHERE w='00:00:00.000'