select * from tb where convert(varchar(10),失效日期,120) between '' and ''
SELECT * FROM tb WHERE CONVERT(CHAR(10),失效日期,21) BETWEEN 开始日期 and 结束日期
--失效日期为空的是不满足 “BETWEEN 开始日期 AND 结束日期” 的,请放心使用 :)
select * from tb where cast(cast(失效日期 as nvarchar) as datetime) between @startdate and @enddate 失效日期为空的数据也能被过滤掉。
ps:第一次见到用text来存日期的……
declare @start datetime, @end datetime select @start='2008-12-05', @end='2008-12-31' select * from tb where convert(datetime,[失效日期]) between @start and @end
docno rev project effective_date obsolete_date mei-01 1 aq 2008-10-01 mei-02 1 de 2008-9-25 2008-10-2 mei-02 2 de 2008-10-2还有一个问题,我输入查询条件的时候还有可能只输入开始日期,还没有结束日期,或者反过来 。
if object_id('[tb]') is not null drop table [tb] go create table [tb]([docno] varchar(6),[rev] int,[project] varchar(2),[effective_date] varchar(10),[obsolete_date] varchar(10)) insert [tb] select 'mei-01',1,'aq','2008-10-01',null union all select 'mei-02',1,'de','2008-9-25','2008-10-2' union all select 'mei-02',2,'de','2008-10-2',nullselect * from [tb]declare @sdate datetime,@edate datetime select @sdate='2008-9-9',@edate='2008-12-25'select * from tb where [obsolete_date] between isnull(nullif(@sdate,''),'1753-01-01') and isnull(nullif(@edate,''),'9999-12-31') --测试结果: /* docno rev project effective_date obsolete_date ------ ----------- ------- -------------- ------------- mei-02 1 de 2008-9-25 2008-10-2(1 行受影响) */
如果真用text存日期的话…… if object_id('[tb]') is not null drop table [tb] go create table [tb]([docno] varchar(6),[rev] int,[project] varchar(2),[effective_date] text,[obsolete_date] text) insert [tb] select 'mei-01',1,'aq','2008-10-01',null union all select 'mei-02',1,'de','2008-9-25','2008-10-2' union all select 'mei-02',2,'de','2008-10-2',nullselect * from [tb]declare @sdate datetime,@edate datetime select @sdate='2008-9-9',@edate='2008-12-25'select * from tb where cast([obsolete_date] as nvarchar) between isnull(nullif(@sdate,''),'1753-01-01') and isnull(nullif(@edate,''),'9999-12-31') --测试结果: /* docno rev project effective_date obsolete_date ------ ----------- ------- -------------- ------------- mei-02 1 de 2008-9-25 2008-10-2(1 行受影响) */
SELECT * FROM tb WHERE CONVERT(CHAR(10),失效日期,21) BETWEEN 开始日期 and 结束日期
--失效日期为空的是不满足 “BETWEEN 开始日期 AND 结束日期” 的,请放心使用 :)
where cast(cast(失效日期 as nvarchar) as datetime) between @startdate and @enddate
失效日期为空的数据也能被过滤掉。
select @start='2008-12-05', @end='2008-12-31'
select * from tb where convert(datetime,[失效日期]) between @start and @end
docno rev project effective_date obsolete_date
mei-01 1 aq 2008-10-01
mei-02 1 de 2008-9-25 2008-10-2
mei-02 2 de 2008-10-2还有一个问题,我输入查询条件的时候还有可能只输入开始日期,还没有结束日期,或者反过来 。
go
create table [tb]([docno] varchar(6),[rev] int,[project] varchar(2),[effective_date] varchar(10),[obsolete_date] varchar(10))
insert [tb]
select 'mei-01',1,'aq','2008-10-01',null union all
select 'mei-02',1,'de','2008-9-25','2008-10-2' union all
select 'mei-02',2,'de','2008-10-2',nullselect * from [tb]declare @sdate datetime,@edate datetime
select @sdate='2008-9-9',@edate='2008-12-25'select * from tb
where [obsolete_date] between isnull(nullif(@sdate,''),'1753-01-01') and isnull(nullif(@edate,''),'9999-12-31')
--测试结果:
/*
docno rev project effective_date obsolete_date
------ ----------- ------- -------------- -------------
mei-02 1 de 2008-9-25 2008-10-2(1 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([docno] varchar(6),[rev] int,[project] varchar(2),[effective_date] text,[obsolete_date] text)
insert [tb]
select 'mei-01',1,'aq','2008-10-01',null union all
select 'mei-02',1,'de','2008-9-25','2008-10-2' union all
select 'mei-02',2,'de','2008-10-2',nullselect * from [tb]declare @sdate datetime,@edate datetime
select @sdate='2008-9-9',@edate='2008-12-25'select * from tb
where cast([obsolete_date] as nvarchar) between isnull(nullif(@sdate,''),'1753-01-01') and isnull(nullif(@edate,''),'9999-12-31')
--测试结果:
/*
docno rev project effective_date obsolete_date
------ ----------- ------- -------------- -------------
mei-02 1 de 2008-9-25 2008-10-2(1 行受影响)
*/
如果sdate或edate为空对结果有影响吗?