表A
Id date
1 2013-10-40 00:00:00
2 2013-10-01 12:00:00
3 2014-01-01 12:09:34
select * from A
where Isdate(date) = 1
and Convert(datetime,date) >= '2014-01-01 00:00:00'
需要结果
2014-01-01 12:09:34
---1行结果受影响
如果运行以上语句会报错，无法将varchar转换成datetime,超出长度限制。，，，
急急，，，，，，，，，，，
------解决方案--------------------
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)
------解决方案--------------------
一开始运行会报错，因为你的where中用了convert函数，把字符串转化为日期型，但肯定有数据不符合日期型，后面通过isdate函数，找到了有问题的日期数据，第一条数据是有问题的：2013-10-40 00:00:00，
日期最多到31号，没有40号的。
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 不是日期
2 2013-10-01 12:00:00 是日期
3 2014-01-01 12:09:34 是日期
(3 行受影响)
*/
------解决方案--------------------
----------------------------------------------------------------
-- Author :fredrickhu(小F，向高手学习)
-- Date :2013-10-22 22:50:08
-- Version:
-- Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (Intel X86)
-- Sep 22 2011 00:28:06
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Id] int,[date] varchar(50))
insert [tb]
select 1,'2013-10-10 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
(select
*
from
tb
where
Isdate(date)=1)t
where
Convert(datetime,date) >= '2014-01-01 00:00:00'
select * from tb
where Isdate(date) = 1
and Convert(datetime,date) >= '2014-01-01 00:00:00'