cast(edittime as datetime) >='2007-05-01'这个是我一条sql语句里面的一个检索条件,
因为是维护已有的系统,内部存在大概20w条数据,edittime为varchar(50)的类型,
edittime字段里面的数据有几种形式表现:2007 8 12;2007-08-12;8 12 2007;最讨厌的是还有一批数据是 '2007-08-12 至 2007-09-23'这样的;
现在检索的时候会出现“从字符串转换为 datetime 时发生语法错误。”的情况;现在这样的检索语句要怎么样写比较好啊?一下是整个sql语句select sum(num) as num,sum(totalpage) as totalpage,
editor from (
select d.num,d.totalpage,c.editor
from t_volumerecord c
inner join (
select a.volumeid,count(b.id) as num,max(b.pagenum2) as totalpage
from t_volumerecord a inner join t_volumeitem b
on a.volumeid = b.volumeid
group by a.volumeid
) d on c.volumeid = d.volumeid where 1=1 and c.editor='***'
and cast(f.edittime as datetime) >='2007-05-01'
and cast(f.edittime as datetime) <'2008-03-01'
) e group by editor
因为是维护已有的系统,内部存在大概20w条数据,edittime为varchar(50)的类型,
edittime字段里面的数据有几种形式表现:2007 8 12;2007-08-12;8 12 2007;最讨厌的是还有一批数据是 '2007-08-12 至 2007-09-23'这样的;
现在检索的时候会出现“从字符串转换为 datetime 时发生语法错误。”的情况;现在这样的检索语句要怎么样写比较好啊?一下是整个sql语句select sum(num) as num,sum(totalpage) as totalpage,
editor from (
select d.num,d.totalpage,c.editor
from t_volumerecord c
inner join (
select a.volumeid,count(b.id) as num,max(b.pagenum2) as totalpage
from t_volumerecord a inner join t_volumeitem b
on a.volumeid = b.volumeid
group by a.volumeid
) d on c.volumeid = d.volumeid where 1=1 and c.editor='***'
and cast(f.edittime as datetime) >='2007-05-01'
and cast(f.edittime as datetime) <'2008-03-01'
) e group by editor
如果只有这三种形式,可以试试:
cast(cast(f.edittime as varchar(10)) as datetime)
你可以查询一下,看看到底有多少种,实在不行,只能建个新表,把数据复制一份再处理了(有些数据可能要手动修改)select edittime from tb
where edittime not like '% % %'
and edittime not like '%-%-%'
and edittime not like '%至%'
这样看行不?
isdate(f.edittime)>0 and cast(cast(f.edittime as varchar(10)) as datetime)另外楼主确实强人,双胞胎,比较厉害。呵呵
insert into @tb select '2008-01-03'
insert into @tb select '2008-01-04'
insert into @tb select '2008-01-05'
insert into @tb select '2008-01-04 至 2008-01-06'
insert into @tb select '2008-01-06'
insert into @tb select '2008-01-07'select * from @tb
where (charindex('至',dt)=0 and dt between '2008-01-04' and '2008-01-06') or
(charindex('至',dt)>0
and (cast(left(dt,10) as datetime) between '2008-01-04' and '2008-01-06')
and (cast(right(dt,10) as datetime) between '2008-01-04' and '2008-01-06'))2008-01-04
2008-01-05
2008-01-04 至 2008-01-06
2008-01-06楼主双包胎?厉害啊