declare @t table (id int,s char(7),e char(7))insert into @t select 1,'2000.01','2000.05' union all select 2,'2000.02','2000.03'SELECT id,s,e,datediff(month,cast(REPLACE(s,'.','-')+'-01' as datetime),cast(REPLACE(e,'.','-')+'-01' as datetime)) from @tid s e ----------- ------- ------- ----------- 1 2000.01 2000.05 4 2 2000.02 2000.03 1
select id,startdate,enddate, (convert(int,substring(enddate,1,4))-convert(int,substring(startdate ,1,4)))*12+convert(int,substring(enddate,6,2))-convert(int,substring(startdate,6,2)) as minu from table
从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
SELECT StartRange, EndRange, BLRQ, DATEDIFF([month], ISNULL(StartRange, '2000.01')
+ '.01', ISNULL(EndRange, '2000.02') + '.01') AS Expr1
FROM ACOFeeListD
WHERE (BLRQ > '2003-05-01') AND (BLRQ < '2003-12-01')
这个是我写的
不加where是对的,能出来,但一加就报哪个错是什么原因呀?
我在sqlserver表里的哪个查询里直接输入这个语句不报错
但是一到查询分析器里就报错/
如下:
(所影响的行数为 714 行)服务器: 消息 242,级别 16,状态 3,行 1
从 char 数据类型到 datetime 数据类型的转换导致 datetime 值越界。
union all select 2,'2000.02','2000.03'SELECT id,s,e,datediff(month,cast(REPLACE(s,'.','-')+'-01' as datetime),cast(REPLACE(e,'.','-')+'-01' as datetime)) from @tid s e
----------- ------- ------- -----------
1 2000.01 2000.05 4
2 2000.02 2000.03 1
(convert(int,substring(enddate,1,4))-convert(int,substring(startdate ,1,4)))*12+convert(int,substring(enddate,6,2))-convert(int,substring(startdate,6,2)) as minu from table