日期:2014-05-17 浏览次数:20473 次
表A
Id date
1 2013-10-40 00:00:00
2 2013-10-01 12:00:00
3 2014-01-01 12:09:34
create table A(Id int, date datetime)
insert into A
select 1, '2013-10-04 00:00:00' union all
select 2, '2013-10-01 12:00:00' union all
select 3, '2014-01-01 12:09:34'
select * from A
where Isdate(date) = 1
and Convert(datetime,date) >= '2014-01-01 00:00:00'
----------------------------
Id date
----------- -----------------------
3 2014-01-01 12:09:34.000
(1 row(s) affected)
if object_id('a') is not null
drop table a
go
create table A(Id int, date varchar(30))
insert into A
select 1, '2013-10-40 00:00:00' union all
select 2, '2013-10-01 12:00:00' union all
select 3, '2014-01-01 12:09:34'
--报错了
select * from A
where Isdate(date) = 1
and Convert(datetime,date) >= '2014-01-01 00:00:00'
/*
Id date
----------- ------------------------------
消息 242,级别 16,状态 3,第 10 行
从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。
*/
--找到有问题的数据
select *,
case when isdate(date) = 1 then '是日期'
when isdate(date) = 0 then '不是日期'
end
from A
--where Isdate(date) = 1
--and Convert(datetime,date) >= '2014-01-01 00:00:00'
/*
Id date
----------- ------------------------------ --------
1 2013-10-40 00:00:00&