select 存期,开户日期,
(case left(存期,1) when 'M' then
dateadd(month,cast(right(存期,2) as int),开户日期)
when 'Y' then
dateadd(year,cast(right(存期,2) as int),开户日期)
end)
from 表
(case left(存期,1) when 'M' then
dateadd(month,cast(right(存期,2) as int),开户日期)
when 'Y' then
dateadd(year,cast(right(存期,2) as int),开户日期)
end)
from 表
create table 表(存期 char(3),开户日期 datetime)
insert 表 select 'M06','2002-1-13'
union all select 'Y01','2000-11-23'
union all select 'M01','2001-5-23'
union all select 'Y03','1999-9-16'
select 存期,开户日期,
(case left(存期,1) when 'M' then
dateadd(month,cast(right(存期,2) as int),开户日期)
when 'Y' then
dateadd(year,cast(right(存期,2) as int),开户日期)
end)
from 表存期 开户日期
---- ------------------------------------------------------ ------------------------------------------------------
M06 2002-01-13 00:00:00.000 2002-07-13 00:00:00.000
Y01 2000-11-23 00:00:00.000 2001-11-23 00:00:00.000
M01 2001-05-23 00:00:00.000 2001-06-23 00:00:00.000
Y03 1999-09-16 00:00:00.000 2002-09-16 00:00:00.000(所影响的行数为 4 行)
create table 表(存期 char(3),开户日期 datetime)
insert 表 select 'M06','2002-1-13'
union all select 'Y01','2000-11-23'
union all select 'M01','2001-5-23'
union all select 'Y03','1999-9-16'--将结果转换成字符型:
select 存期,convert(char(10),开户日期,120) as 开户日期,
convert(char(10),(case left(存期,1) when 'M' then
dateadd(month,cast(right(存期,2) as int),开户日期)
when 'Y' then
dateadd(year,cast(right(存期,2) as int),开户日期)
end) ,120) as 结束日期
from 表存期 开户日期 结束日期
---- ---------- ----------
M06 2002-01-13 2002-07-13
Y01 2000-11-23 2001-11-23
M01 2001-05-23 2001-06-23
Y03 1999-09-16 2002-09-16(所影响的行数为 4 行)
go
insert into 表 select 'M06' ,cast('2002-1-13'as datetime)
union select 'Y01' ,cast('2000-11-23'as datetime)
union select 'M01' ,cast('2001-5-23'as datetime)
union select 'Y03' ,cast('1999-9-16'as datetime)goselect 存期,开户日期,(case substring(存期,1,1) when 'M'then
dateadd(month,cast(substring(存期,2,2)as int), cast(开户日期 as datetime) )
else dateadd(year,cast(substring(存期,2,2)as int), cast(开户日期 as datetime) ) end) as 到期from 表