declare @t1 datetime,@t2 datetime,@t3 datetime,@t4 datetime select @t1=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',1,10)) select @t2=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',12,10)) select @t3=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',23,10)) select @t4=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',34,10)) select * from tablename where (curdate not between @t1 and @t2) and (curdate not between @t3 and @t4)
vb中有个函数是将字符串砍成一段一段的,不知sql有没有?
alter function dbo.IsVal (@chv varchar(8000) ,@dat datetime) returns bit as begin declare @return bit declare @t table (start datetime,endt datetime) declare @tmp varchar(8000) declare @rtmp varchar(8000) declare @start varchar(20) declare @end varchar(20) declare @p int set @return=0 set @p=1 set @rtmp=@chv while charindex(',',@rtmp,1)>=1 begin set @p=charindex(',',@rtmp,1) set @tmp=left(@rtmp,@p-1) set @rtmp=right(@rtmp,len(@rtmp)-@p) set @p=charindex('-',@tmp,1) set @start=left(@tmp,@p-1) set @end=right(@tmp,len(@tmp)-@p) insert into @t values(cast(@start as datetime),cast(@end as datetime)) end set @tmp=@rtmp --set @rtmp=right(@rtmp,len(@rtmp)-@p) set @p=charindex('-',@tmp,1) set @start=left(@tmp,@p-1) set @end=right(@tmp,len(@tmp)-@p) insert into @t values(cast(@start as datetime),cast(@end as datetime)) if exists (select * from @t where @dat between start and endt) set @return=1 return @return end 调用 select * from table where dbo.isval(fdatestr,getdate())
我偏要说,这种设计实在是很不专业,规范的设计应该是有一张停业记录表 tbPause(storeID int,puaseFrom smalldatetime,pauseTo smalldatetime) 这样,这个表里对某个店的停业记录可以不受你那字段长度的限制,而且查询也很方便: select ID from tbStore where id not in ( select storeID from tbPause where getdate() between pauseFrom and pauseTo )以上假设有一张商店表tbStore(ID int , otherField....) tbStore.id 与 tbPause.storeID关联
declare @a varchar(100) set @a = '1999.01.20-1999.06.20,2002.01.20-2002.9.21' exec('select * from table1 where (datefield between '''+replace(replace(@a,',',') or (datefield between '''),'-',''' and ''')+''')')
上面会少最后一天,要把datefield用convert替换成字符串,我忘了参数. 如: convert(varchar(10),datefield,120) between '1999-01-02' and '1999-06-20'
select @t1=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',1,10))
select @t2=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',12,10))
select @t3=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',23,10))
select @t4=convert(datetime,substring('1999.01.20-1999.06.20,2002.01.20-2002.9.21',34,10))
select * from tablename where (curdate not between @t1 and @t2) and (curdate not between @t3 and @t4)
(@chv varchar(8000)
,@dat datetime)
returns bit
as
begin
declare @return bit
declare @t table (start datetime,endt datetime)
declare @tmp varchar(8000)
declare @rtmp varchar(8000)
declare @start varchar(20)
declare @end varchar(20)
declare @p int
set @return=0
set @p=1
set @rtmp=@chv
while charindex(',',@rtmp,1)>=1
begin
set @p=charindex(',',@rtmp,1)
set @tmp=left(@rtmp,@p-1)
set @rtmp=right(@rtmp,len(@rtmp)-@p)
set @p=charindex('-',@tmp,1)
set @start=left(@tmp,@p-1)
set @end=right(@tmp,len(@tmp)-@p)
insert into @t values(cast(@start as datetime),cast(@end as datetime))
end
set @tmp=@rtmp
--set @rtmp=right(@rtmp,len(@rtmp)-@p)
set @p=charindex('-',@tmp,1)
set @start=left(@tmp,@p-1)
set @end=right(@tmp,len(@tmp)-@p)
insert into @t values(cast(@start as datetime),cast(@end as datetime))
if exists (select * from @t where @dat between start and endt)
set @return=1
return @return
end
调用
select * from table
where dbo.isval(fdatestr,getdate())
tbPause(storeID int,puaseFrom smalldatetime,pauseTo smalldatetime)
这样,这个表里对某个店的停业记录可以不受你那字段长度的限制,而且查询也很方便:
select ID from tbStore where id not in (
select storeID from tbPause where getdate() between pauseFrom and pauseTo
)以上假设有一张商店表tbStore(ID int , otherField....)
tbStore.id 与 tbPause.storeID关联
set @a = '1999.01.20-1999.06.20,2002.01.20-2002.9.21'
exec('select * from table1 where (datefield between '''+replace(replace(@a,',',') or (datefield between '''),'-',''' and ''')+''')')
如:
convert(varchar(10),datefield,120) between '1999-01-02' and '1999-06-20'