tb1 中有三个字段ID,DT1,DT2 全部为varchar类型。
其中DT1 ,DT2 为时间格式,但在表中都是 varchar 类型。。我根据tb1 创建视图
create view tv1
SELECT ID, CAST(DT1 AS datetime) AS DT1, CAST(DT2 AS datetime) AS DT2
FROM dbo.tb1 我在程序中用
select count(1) from tv1 where DT1>='2011-07-01 00:00:00' and DT2 <='2011-08-26 23:59:59'结果报错 为 "从字符串转换日期和/或时间时,转换失败。"但我把该 SQL 语句直接放在SQL中执行,该语句不报错,能正确执行。。
其中DT1 ,DT2 为时间格式,但在表中都是 varchar 类型。。我根据tb1 创建视图
create view tv1
SELECT ID, CAST(DT1 AS datetime) AS DT1, CAST(DT2 AS datetime) AS DT2
FROM dbo.tb1 我在程序中用
select count(1) from tv1 where DT1>='2011-07-01 00:00:00' and DT2 <='2011-08-26 23:59:59'结果报错 为 "从字符串转换日期和/或时间时,转换失败。"但我把该 SQL 语句直接放在SQL中执行,该语句不报错,能正确执行。。
我记得C#中是cstring
--你的语句测试通过了啊,没有报错!
create table tb1
(
id int,
DT1 varchar(100),
DT2 varchar(100)
)
insert into tb1 values(1,'2011-07-10','2011-08-01')
insert into tb1 values(2,'2011-07-14','2011-08-04')
insert into tb1 values(3,'2011-07-17','2011-08-06')create view tv1
as
SELECT ID, CAST(DT1 AS datetime) AS DT1, CAST(DT2 AS datetime) AS DT2
FROM dbo.tb1 select * from tv1ID,DT1,DT2
1,2011-07-10 00:00:00.000,2011-08-01 00:00:00.000
2,2011-07-14 00:00:00.000,2011-08-04 00:00:00.000
3,2011-07-17 00:00:00.000,2011-08-06 00:00:00.000(3 行受影响)
select count(1) from tv1 where CAST(DT1 AS datetime) >='2011-07-01 00:00:00' and CAST(DT2 AS datetime) <='2011-08-26 23:59:59'该语句在SQL中能执行..
还有,select count(1) from tv1 having DT1>=cast('2011-07-01 00:00:00' as datetime) and DT2 <=cast('2011-08-26 23:59:59' as datetime)
我的开发环境是 vs 2008 + mssql 2008
SELECT ID, DT1, DT2
FROM tb1 a where isdate(a.dt1)=0 or isdate(a.dt2)=0
看看有没有这样的记录,怀疑你表里有非日期数据,导致了转换失败(CAST(DT1 AS datetime) AS DT1)
到程序中还是报相同的错误。