还是截取字符串的问题, zlp321002(她是我的唯一.),wangkenping(找有感觉的妹妹)再帮我改一下

[sywcf]

原贴http://community.csdn.net/Expert/topic/4457/4457896.xml?temp=.7108423

上午的贴有些问题，这里重新更正一下：
表名：tb
字段名:memo
字段值： 注意格式不是：k(2005.01.02-2005.10.10)而是
k(05.01.02-05.08.10)
k(03.05.20-04.10.02)
k(04.10.09-05.09.09)

...............
格式：k(xyz-abc)

想取出abc部分的最大年份与最大月份

select year=max(year(cast(substring(memo,charindex('-',memo)+1,(charindex(')',memo,charindex('-',memo)) - charindex('-',memo) -1)) as datetime))),
month=max(month(cast(substring(memo,charindex('-',memo)+1,(charindex(')',memo,charindex('-',memo)) - charindex('-',memo) -1)) as datetime)))
from @tb

用这个总是提示我：
将 expression 转换为数据类型 datetime 时发生算术溢出错误。

怎么改一下啊？

[samfeng_2003]

declare @tb table(memo varchar(40))
insert into @tb select 'k(05.1.10-05.10.1)'
union all select 'k(04.5.1-04.11.11)'
union all select 'k(05.10.10-05.12.10)'



select
max(year(cast(stuff(replace(replace(replace(replace(memo,'-',','),'.','-'),'k(',''),')',''),
1,charindex(',',replace(replace(replace(replace(memo,'-',','),'.','-'),'k(',''),')','')),'') as datetime)))
as maxyear,
max(month(cast(stuff(replace(replace(replace(replace(memo,'-',','),'.','-'),'k(',''),')',''),
1,charindex(',',replace(replace(replace(replace(memo,'-',','),'.','-'),'k(',''),')','')),'') as datetime)))
as maxmonth
from @tb

maxyear maxmonth
----------- -----------
2005 12

（所影响的行数为 1 行）

[zlp321002]

--测试环境
Create table tb(memo varchar(100))
insert into tb select 'k(05.1.10-05.10.1)'
union all select 'k(04.5.1-04.11.11)'
union all select 'k(05.10.10-05.12.10)'

--查询
select * from tb A
where not exists
(select 1 from tb where year(left(stuff(memo,1,charindex('-',memo),''),len(stuff(memo,1,charindex('-',memo),''))-1 ) )>year(left(stuff(A.memo,1,charindex('-',A.memo),''),len(stuff(A.memo,1,charindex('-',A.memo),''))-1 ) )
or month(left(stuff(memo,1,charindex('-',memo),''),len(stuff(memo,1,charindex('-',memo),''))-1 ) )>month(left(stuff(A.memo,1,charindex('-',A.memo),''),len(stuff(A.memo,1,charindex('-',A.memo),''))-1 ) )
)

--结果

Memo
--------
k(05.10.10-05.12.10)

（所影响的行数为 1 行）

[samfeng_2003]

但是你的那种方法也可以的啊

[sywcf]

先澄清一下，那句也不是我写的，是wangkenping(找有感觉的妹妹)帮我写的.
但是那句对 k(2005.01.02-2005.10.10)这个好用，那是因为我写错了。

其实格式是：k(05.01.02-05.10.10)这样的，那就不行了，就提示错了.

[sywcf]

你们写的都对，可能是我的数据库里的记录有问题。谢谢

[sywcf]

找到原因了，我的数据库里，还有这样的格式：
k(05.1.10-05.10.1)#aaa

samfeng_2003(凤翼天翔) 写的提示我：从字符串转换为 datetime 时发生语法错误。

请高手再给看看，怎么改吧

[samfeng_2003]

已经加上case才行，因为必须对同字段前和后的日期的最大年月进行比较才行。我列出了会出现这种情况的一段数据。

declare @tb table(memo varchar(40))
insert into @tb select 'k(05.1.10-06.1.1)'
union all select 'k(04.5.1-06.3.11)'
union all select 'k(05.10.10-06.9.10)'
union all select 'k(05.1.12-06.1.1)#aaa'

select
case when
max(year(cast(replace(substring(memo,charindex('-',memo)+1,charindex(')',memo)-charindex('-',memo)-1),'.','-')
as datetime)))>
max(year(cast(replace(substring(memo,charindex('(',memo)+1,charindex('-',memo)-charindex('(',memo)-1),'.','-')
as datetime))) then
max(year(cast(replace(substring(memo,charindex('-',memo)+1,charindex(')',memo)-charindex('-',memo)-1),'.','-')
as datetime))) else
max(year(cast(replace(substring(memo,charindex('(',memo)+1,charindex('-',memo)-charindex('(',memo)-1),'.','-')
as datetime))) end as max_year,
case when
max(month(cast(replace(substring(memo,charindex('-',memo)+1,charindex(')',memo)-charindex('-',memo)-1),'.','-')
as datetime)))>
max(month(cast(replace(substring(memo,charindex('(',memo)+1,charindex('-',memo)-charindex('(',memo)-1),'.','-')
as datetime))) then
max(month(cast(replace(substring(memo,charindex('-',memo)+1,charindex(')',memo)-charindex('-',memo)-1),'.','-')
as datetime))) else
max(month(cast(replace(substring(memo,charindex('(',memo)+1,charindex('-',memo)-charindex('(',memo)-1),'.','-')
as datetime))) end as max_month
from @tb

max_year max_month
----------- -----------
2006 10

（所影响的行数为 1 行）