同意楼上,使用between .. and .. 还有,看一下你的f_CXRQ存储的是什么格式的数据吧,如果是'2005-05-01'这样的就: select * from tablename where f_CXRQ between '2005-05-15' and '2005-07-02' 否则注意转换一下就可以。
select * from 表 where convert(char(5),f_CXRQ,101) between '05/15' and '07/02''
select * from 表 where convert(char(5),f_CXRQ,101) between '05/15' and '07/02'
--輸入年份的 create procedure p_Total @StartDateTime varchar(20), @EndDateTime varchar(20) as select * from tablename where convert(char10),f_CXRQ,120) between @StartDateTime and EndDateTime go --執行存儲過程 exec p_Total '2005-05-15','2005-07-02' --沒有年份的 create procedure p_Total @StartDateTime varchar(20), @EndDateTime varchar(20) as select * from tablename where right(convert(char10),f_CXRQ,120),5) between @StartDateTime and EndDateTime --執行存儲過程 exec p_Total '05-15','07-02'
如果日期為跨年度的則應為這樣:declare @begindate varchar(10),@enddate varchar(10) select @begindate='12/01',@enddate='01/10'select * from 表 where (case when @begindate<@enddate then case when convert(char(5),f_CXRQ,101) between @begindate and @enddate then 1 else 0 end when convert(char(5),f_CXRQ,101)>@enddate and convert(char(5),f_CXRQ,101)<@begindate then 0 else 1 end) = 1
表内数据:f_KHBH f_KHMC f_CSRQ 0001 客户01 1970-01-01 0002 客户02 1967-05-13 0003 客户03 1978-09-12 0004 客户04 1960-04-29执行:SELECT * FROM dbo.d_KHXX WHERE (CONVERT(char(5), f_CSRQ, 101) BETWEEN '01-01' AND '12-31') 后只统计出0002到0004号记录,没有0001客户的信息,也就是说没有 1970-01-01 的记录,郁闷...
convert函數中最後一個參數是101 返回的格式是'01/01' 如果條件是'01-01',則用110SELECT * FROM dbo.d_KHXX WHERE (CONVERT(char(5), f_CSRQ, 110) BETWEEN '01-01' AND '12-31')
还有,看一下你的f_CXRQ存储的是什么格式的数据吧,如果是'2005-05-01'这样的就:
select * from tablename
where f_CXRQ between '2005-05-15' and '2005-07-02'
否则注意转换一下就可以。
select * from 表
where convert(char(5),f_CXRQ,101) between '05/15' and '07/02''
where convert(char(5),f_CXRQ,101) between '05/15' and '07/02'
create procedure p_Total
@StartDateTime varchar(20),
@EndDateTime varchar(20)
as
select * from tablename
where convert(char10),f_CXRQ,120) between @StartDateTime and EndDateTime
go
--執行存儲過程
exec p_Total '2005-05-15','2005-07-02'
--沒有年份的
create procedure p_Total
@StartDateTime varchar(20),
@EndDateTime varchar(20)
as
select * from tablename
where right(convert(char10),f_CXRQ,120),5) between @StartDateTime and EndDateTime
--執行存儲過程
exec p_Total '05-15','07-02'
select @begindate='12/01',@enddate='01/10'select * from 表 where
(case when @begindate<@enddate then
case when convert(char(5),f_CXRQ,101) between @begindate and @enddate then 1 else 0 end
when convert(char(5),f_CXRQ,101)>@enddate and convert(char(5),f_CXRQ,101)<@begindate
then 0 else 1 end) = 1
0001 客户01 1970-01-01
0002 客户02 1967-05-13
0003 客户03 1978-09-12
0004 客户04 1960-04-29执行:SELECT * FROM dbo.d_KHXX
WHERE (CONVERT(char(5), f_CSRQ, 101) BETWEEN '01-01' AND '12-31')
后只统计出0002到0004号记录,没有0001客户的信息,也就是说没有 1970-01-01 的记录,郁闷...
如果條件是'01-01',則用110SELECT * FROM dbo.d_KHXX
WHERE (CONVERT(char(5), f_CSRQ, 110) BETWEEN '01-01' AND '12-31')