declare @a int
set @a = 27
Select * from (Select kcode,max(op_date) as op_date from 表) aa
where datediff(day,cast(year(getdate()) as char(4)) + '-' + op_date,getdate())>= @a
set @a = 27
Select * from (Select kcode,max(op_date) as op_date from 表) aa
where datediff(day,cast(year(getdate()) as char(4)) + '-' + op_date,getdate())>= @a
As
Select * from (Select kcode,max(op_date) as op_date from 表) aa
where datediff(day,cast(year(getdate()) as char(4)) + '-' + op_date,getdate())>= @a
go-- 调用 Exec test 2
-- Exec test 27
where convert(datetime,'2003-'+op_date)=dateadd(day,-2,convert(char(8),getdate(),112))
set @i=2
select * from grd_det
where datediff(day,grd_jlrq,getdate())=@i
我觉得是直接引用表名阿
按你的方法试了,报错如下:
Syntax error converting datetime from character string.另外,我op_date 日期都是如 2003-10-01 14:38:14 这样的数据
按你的方法,遇到以下问题呢
Column 'ktrade.dbo.ktrade_info.client_bh' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
from @yourtablename
select datediff(day,convert(datetime,convert(char,year(getDate()))+op_date),getDate())<=@differenceday其中:@yourtablename是你的表名
@differenceday是最近的几天
declare @t table (k_code varchar(3),op_date varchar(5))
insert @t values('001','09-15')
insert @t values('002','09-15')
insert @t values('003','09-15')
insert @t values('007','09-15')
insert @t values('001','09-30')
insert @t values('004','09-30')
insert @t values('009','09-30')
insert @t values('005','10-01')
insert @t values('006','10-09')
insert @t values('007','10-11')
set @day=28
select a.k_code,left(a.dt,5) as op_date from (select k_code,op_date+'-'+'03' as dt from @t)a where datediff(day,convert(datetime,a.dt,10),getdate()) <=@day
As
Select * from (Select kcode,max(op_date) as op_date from 表) aa
where datediff(day,cast(op_date as datetime),getdate())>= @a
go
declare @t table (k_code varchar(3),op_date varchar(5))
insert @t values('001','09-15')
insert @t values('002','09-15')
insert @t values('003','09-15')
insert @t values('007','09-15')
insert @t values('001','09-30')
insert @t values('004','09-30')
insert @t values('009','09-30')
insert @t values('005','10-01')
insert @t values('006','10-09')
insert @t values('007','10-11')
set @day=2select * from @t
where op_date=substring(convert(varchar(10),getdate() - @day,120),6,5)
select * from ktrade.dbo.ktrade_info
where convert(varchar(10),op_date,120)=dateadd(day,-10,convert(varchar(10), getdate(),120))
where convert(varchar(10),op_date,120)=convert(varchar(10),dateadd(day,-10, getdate()),120)
快好了,但还有点要求,如:kcode op_date(类型:datetime)
----------------------------
001 2003-10-01 14:38:14.000
001 2003-10-09 14:38:14.000
001 2003-10-12 23:23:23.123如果输入的数字为 -2 则可以显示: 001 2003-10-12 23:23:23.123
这是正确的!但输入的数字为 -17 时 就应该无法找到相应的记录了,因为 001 最后一次的记录时间是
2003-10-12 23:23:23.123,离当天的时间差是-2 而不是-17 ,你现在的办法却可以查到时间为2003-10-01 14:38:14.000 的记录了。请再帮看看!谢!