select * from table1 where (rtrim(fyear)+'-'+right('0'+rtrim(fmonth),2)) between '2006-09' and '2006-11'
设计的时候需要考虑查询如果查询需要转换,那不如就放一个DATETIME字段
我想到将年度月份转换成日期再比较?和谁比较?select * from table1 where (cast(fyear as varchar(4)) + '-' + rtrim(cast(fmonth as varchar(2))) + '01') >= '2006-09-01' adn (cast(fyear as varchar(4)) + '-' + rtrim(cast(fmonth as varchar(2))) + '01') <= '2006-11-30'
我想到将年度月份转换成日期再比较?和谁比较?select * from table1 where (cast(fyear as varchar(4)) + '-' + rtrim(cast(fmonth as varchar(2))) + '01') >= '2006-09-01' and (cast(fyear as varchar(4)) + '-' + rtrim(cast(fmonth as varchar(2))) + '01') <= '2006-11-30'
提供一个跨年度的查询的SQL处理方法如下:select * from table1 where (fyear>@bYear and fyear<eYear) or (fyear=@bYear and fmonth>=@bmonth) or (fyear=@eYear and fmonth<=@emonth)
先谢谢各位了 tx1icenhe(冒牌马可 V0.3) WHERE fyear =2006 AND fmonth BETWEEN 9 AND 11 你这样的写法,如果是跨年度的怎么办 现想查询2006年9月份至2007年3月份之间的数据查询的条件如何写(年月是变化的)
现想查询2006年9月份至2007年3月份之间的数据查询的条件如何写(年月是变化的) ----------------------------------------------------------------------------------------- select * from table1 where (fyear>@bYear and fyear<eYear) or (fyear=@bYear and fmonth>=@bmonth) or (fyear=@eYear and fmonth<=@emonth)==>select * from table1 where (fyear>2006 and fyear<2007) or (fyear=2006 and fmonth>=9) or (fyear=2007 and fmonth<=3)
--不曉得這樣對不~~~declare @syear int,@smonth int,@eyear int,@emonth int select @syear=2006,@smonth=9,@eyear=2007,@emonth=3set @emonth=(@eyear-@syear)*12+@emonthselect * from T where (fyear-@syear)*12+fmonth between @smonth and @emonth
AND fmonth BETWEEN 9 AND 11
*
from
table1
where
(rtrim(fyear)+'-'+right('0'+rtrim(fmonth),2)) between '2006-09' and '2006-11'
*
from
table1
where
(fyear>@bYear and fyear<eYear)
or
(fyear=@bYear and fmonth>=@bmonth)
or
(fyear=@eYear and fmonth<=@emonth)
tx1icenhe(冒牌马可 V0.3)
WHERE fyear =2006
AND fmonth BETWEEN 9 AND 11
你这样的写法,如果是跨年度的怎么办
现想查询2006年9月份至2007年3月份之间的数据查询的条件如何写(年月是变化的)
-----------------------------------------------------------------------------------------
select
*
from
table1
where
(fyear>@bYear and fyear<eYear)
or
(fyear=@bYear and fmonth>=@bmonth)
or
(fyear=@eYear and fmonth<=@emonth)==>select
*
from
table1
where
(fyear>2006 and fyear<2007)
or
(fyear=2006 and fmonth>=9)
or
(fyear=2007 and fmonth<=3)
select @syear=2006,@smonth=9,@eyear=2007,@emonth=3set @emonth=(@eyear-@syear)*12+@emonthselect * from T
where (fyear-@syear)*12+fmonth between @smonth and @emonth