if object_id('标签表')is not null drop table 标签表 go CREATE TABLE 标签表(TagID INT, Name datetime) INSERT 标签表 SELECT 1, '2009-09-30 23:59:59' select * from 标签表TagID Name ----------- ----------------------- 1 2009-09-30 23:59:59.000(1 行受影响)
select cast('2009-09-30 23:59:59' as datetime) /*----------------------- 2009-09-30 23:59:59.000(1 行受影响) */
除非改用varchar
if object_id('标签表')is not null drop table 标签表
go
CREATE TABLE 标签表(TagID INT, Name datetime)
INSERT 标签表 SELECT
1, '2009-09-30 23:59:59' select * from 标签表TagID Name
----------- -----------------------
1 2009-09-30 23:59:59.000(1 行受影响)
/*-----------------------
2009-09-30 23:59:59.000(1 行受影响)
*/
--毫秒会舍入的
--A. 测试 datetime 精度问题
DECLARE @t TABLE(date char(21))
INSERT @t SELECT '1900-1-1 00:00:00.000'
INSERT @t SELECT '1900-1-1 00:00:00.001'
INSERT @t SELECT '1900-1-1 00:00:00.009'
INSERT @t SELECT '1900-1-1 00:00:00.002'
INSERT @t SELECT '1900-1-1 00:00:00.003'
INSERT @t SELECT '1900-1-1 00:00:00.004'
INSERT @t SELECT '1900-1-1 00:00:00.005'
INSERT @t SELECT '1900-1-1 00:00:00.006'
INSERT @t SELECT '1900-1-1 00:00:00.007'
INSERT @t SELECT '1900-1-1 00:00:00.008'
SELECT date,转换后的日期=CAST(date as datetime) FROM @tdate 转换后的日期
1900-1-1 00:00:00.000 1900-01-01 00:00:00.000
1900-1-1 00:00:00.001 1900-01-01 00:00:00.000
1900-1-1 00:00:00.009 1900-01-01 00:00:00.010
1900-1-1 00:00:00.002 1900-01-01 00:00:00.003
1900-1-1 00:00:00.003 1900-01-01 00:00:00.003
1900-1-1 00:00:00.004 1900-01-01 00:00:00.003
1900-1-1 00:00:00.005 1900-01-01 00:00:00.007
1900-1-1 00:00:00.006 1900-01-01 00:00:00.007
1900-1-1 00:00:00.007 1900-01-01 00:00:00.007
1900-1-1 00:00:00.008 1900-01-01 00:00:00.007
/*-----------------------
2009-10-01 00:00:00(1 行受影响)
*/