27,580
社区成员
发帖
与我相关
我的任务
分享
DECLARE @time_b DATETIME,@time_a DATETIME,@time_c datetime
SELECT @time_a='2007-10-5',@time_b='2008-1-2'
@time_c=CASE
WHEN CHARINDEX('d',aa)>0 THEN
DATEADD(dd,CAST(REPLACE(aa,'d','') AS INT),@time_a)
WHEN CHARINDEX('m',aa)>0 THEN
DATEADD(mm,CAST(REPLACE(aa,'m','') AS INT),@time_a)
WHEN CHARINDEX('year',aa)>0 THEN
DATEADD(yy,CAST(REPLACE(aa,'year','') AS INT),@time_a)
END
IF object_id('tb','u') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb(aa varchar(20))
insert tb select '22D'
union all select '3M'
union all select '1YEAR'
GO
DECLARE @time_b DATETIME,@time_a DATETIME
SELECT @time_a='2007-10-5',@time_b='2008-1-2'
SELECT * FROM tb a
WHERE CASE
WHEN CHARINDEX('d',aa)>0 THEN
DATEADD(dd,CAST(REPLACE(aa,'d','') AS INT),@time_a)
WHEN CHARINDEX('m',aa)>0 THEN
DATEADD(mm,CAST(REPLACE(aa,'m','') AS INT),@time_a)
WHEN CHARINDEX('year',aa)>0 THEN
DATEADD(yy,CAST(REPLACE(aa,'year','') AS INT),@time_a)
END
>@time_b
declare @tb table(aa varchar(10))
insert into @tb select '22D'
insert into @tb select '3M'
insert into @tb select '1YEAR'
select aa,
[day]=case when right(aa,1)='D' then replace(aa,'D','')
when right(aa,1)='M' then replace(aa,'M','')*30
when right(aa,4)='YEAR' then replace(aa,'YEAR','')*365 end
from @tb
declare @t table(v varchar(20))
insert @t select '22D'
union all select '3M'
union all select '1YEAR'
select a.* from @t a
inner join
(
select v='d',n=1
union
select v='m',n=30
union
select v='year',n=365
) b
on charindex(b.v,a.v)>0
where datediff(dd,'2007-9-1','2008-3-5')>=cast(replace(a.v,b.v,'') as int) * n