select convert(varchar(30),cast(CAST(time字段 as DATE) as datetime),121)
select cast(convert(varchar(10),time as datetime)
修改如下: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'
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)
select cast(convert(varchar(10),time as datetime)
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'