create procedure proc_open @daying datetime=null --创建一个输入参数,默认为空 as declare @dw int ,@dayed datetime if @daying is null --如果是NULL就赋值为当前时间 set @daying=getdate() SET DATEFIRST 1 select @dw=datepart(dw,@daying) --找出指定时间是本周的第几天 set @dayed=datediff(dd,-@dw+1,@daying) --找出本周第一天的日期 select * from cardinfo where opendate>=@dayed and opendate <=@daying --通过本周第一天的日期和所输入的日期为区间查找该表数据 go
存储过程最后的参数换成dateadd(d,@dw,@dayed)
datediff返回整型,怎么能赋给一个datetime型变量? 而且求本周第一天的方法也是不对的。 create procedure proc_open @daying datetime=null --创建一个输入参数,默认为空 as declare @dw int ,@dayed datetime if @daying is null --如果是NULL就赋值为当前时间 set @daying=getdate()
select @dw=datepart(dw,@daying) --找出指定时间是本周的第几天 set @dayed=dateadd(dd,-(@dw+5)%7,@daying) --找出本周第一天的日期 select * from cardinfo where opendate>=@dayed and opendate <=@daying --通过本周第一天的日期和所输入的日期为区间查找该表数据 go
因此要么用7楼的方法, 要么把那句改为: set @dayed=convert(varchar(10),dateadd(dd,-(@dw+5)%7,@daying),120)
应该不会。给出出错信息。 drop procedure proc_open create procedure proc_open @daying datetime=null --创建一个输入参数,默认为空 as select @daying go exec proc_open '2008-12-06' /* 2008-12-06 00:00:00.000 */ exec proc_open '20081206' /* 2008-12-06 00:00:00.000 */
时间输出没问题了,但是我最终是需要把这两个事件作为where条件后门的限制来进行查找 select * from cardinfo where opendate>=@dayed and opendate <=@daying 我这个where后面这样来判断事件区间可行么, 或者这样应该意义一样把 where opendate between @dayed and @daying
but I wonder if u can get the wanted result.for example, when @daying='2008-12-06', you use 'opendate>=@dayed and opendate <=@daying ' or 'opendate between @dayed and @daying' as where clause, then you can get the records whose opendate like '2008-12-05 23:59:59','2008-12-06 00:00:00',... but you CANNOT get those opendate like '2008-12-06 12:00:00' !Be sure what you need.
maybe you can try:where opendate>=@dayed and opendate<convert(varchar(10),dateadd(d,1,@daying),120) --or: where opendate between @dayed and dateadd(ms,-1,convert(varchar(10),dateadd(d,1,@daying),120))
@daying datetime=null --创建一个输入参数,默认为空
as
declare @dw int ,@dayed datetime
if @daying is null --如果是NULL就赋值为当前时间
set @daying=getdate()
SET DATEFIRST 1
select @dw=datepart(dw,@daying) --找出指定时间是本周的第几天
set @dayed=datediff(dd,-@dw+1,@daying) --找出本周第一天的日期
select * from cardinfo where opendate>=@dayed and opendate <=@daying --通过本周第一天的日期和所输入的日期为区间查找该表数据
go
而且求本周第一天的方法也是不对的。
create procedure proc_open
@daying datetime=null --创建一个输入参数,默认为空
as
declare @dw int ,@dayed datetime
if @daying is null --如果是NULL就赋值为当前时间
set @daying=getdate()
select @dw=datepart(dw,@daying) --找出指定时间是本周的第几天
set @dayed=dateadd(dd,-(@dw+5)%7,@daying) --找出本周第一天的日期
select * from cardinfo where opendate>=@dayed and opendate <=@daying --通过本周第一天的日期和所输入的日期为区间查找该表数据
go
exec proc_open '2008-12-06'
调用谢谢帮在想想
这个刚才测试找出来好像不对哦
/*
2008-12-01 00:00:00.000
*/
日期是对的,但时间多余了。
declare @dw int
set @dw=datepart(dw,getdate())
select dateadd(d,-(@dw+5)%7,getdate())
/*
2008-12-01 14:20:49.700
*/
我输入参数这个格式怎么是无法识别
exec proc_open '2008-12-06'
只有使用默认值才不出现问题
exec proc_open
要么把那句改为:
set @dayed=convert(varchar(10),dateadd(dd,-(@dw+5)%7,@daying),120)
应该不会。给出出错信息。
drop procedure proc_open
create procedure proc_open
@daying datetime=null --创建一个输入参数,默认为空
as
select @daying
go exec proc_open '2008-12-06'
/*
2008-12-06 00:00:00.000
*/
exec proc_open '20081206'
/*
2008-12-06 00:00:00.000
*/
select *
from cardinfo
where opendate>=@dayed and opendate <=@daying
我这个where后面这样来判断事件区间可行么,
或者这样应该意义一样把
where opendate between @dayed and @daying
when @daying='2008-12-06',
you use 'opendate>=@dayed and opendate <=@daying ' or 'opendate between @dayed and @daying' as where clause,
then you can get the records whose opendate like '2008-12-05 23:59:59','2008-12-06 00:00:00',...
but you CANNOT get those opendate like '2008-12-06 12:00:00' !Be sure what you need.
--or:
where opendate between @dayed and dateadd(ms,-1,convert(varchar(10),dateadd(d,1,@daying),120))
那就是最后where后的事件需要转换成varchar类型么?
DBMS will does it AUTOMATICALLY.