表中数据是按期间保存的,现在要取出2009年10月至2010年1月之间的数据怎么查询?
create table #t
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,40
union all Select 2009,12,50
union all Select 2010,1,60
union all Select 2010,2,70--select * from #t where fyear?? and fperiod ??--drop table #t
create table #t
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,40
union all Select 2009,12,50
union all Select 2010,1,60
union all Select 2010,2,70--select * from #t where fyear?? and fperiod ??--drop table #t
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,40
union all Select 2009,12,50
union all Select 2010,1,60
union all Select 2010,2,70select * from #t
where ltrim(fyear)+right('0'+ltrim(fmonth),2) between '200910' and '201001'
drop table #t
fyear fmonth fqty
----------- ----------- -----------
2009 10 30
2009 11 40
2009 12 50
2010 1 60(4 行受影响)
*
from
tb
where
cast(fyear as varchar(10))+right('0'+cast(fmonth as varchar(10)),2) between '200910' and '201001'
from #t where cast(fyear as varchar(10))+'-'+cast(fmonth as varchar(10)) between '2009-10' and '2010-1'
那就用convert转换一下
select convert(varchar(6),@s,112)
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,40
union all Select 2009,12,50
union all Select 2010,1,60
union all Select 2010,2,70select * from
(
select ltrim(fyear)+'-'+ltrim(fmonth)+'-'+ltrim(fqty) as date from #t
)
tt
where date between '2009-10-01' and '2010-01-01'code]
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t1 (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,4
union all Select 2009,12,5
union all Select 2010,1,6
union all Select 2010,2,7select convert(varchar(6),date,112) '日期' from
(
select cast(convert(varchar(10),ltrim(fyear)+'-'+ltrim(fmonth)+'-'+ltrim(fqty),120) as datetime) as date from #t1
)
tt
where date between '2009-10-01' and '2010-01-01'
日期
------
200910
200911
200912(3 行受影响)
(
fyear int ,
fmonth int ,
fqty int
)
insert into #t (fyear,fmonth,fqty)
Select 2009,8,10
union all Select 2009,9,20
union all Select 2009,10,30
union all Select 2009,11,40
union all Select 2009,12,50
union all Select 2010,1,60
union all Select 2010,2,70select convert(varchar(6),date,112) '日期',fqty from
(
select cast(convert(varchar(10),ltrim(fyear)+'-'+ltrim(fmonth)+'-01',120) as datetime) as date,fqty from #t
)
tt
where date between '2009-10-01' and '2010-01-01'
日期 fqty
------ -----------
200910 30
200911 40
200912 50
201001 60(4 行受影响)
where ltrim(fyear)+right('0'+ltrim(fmonth),2) between convert(varchar(7),'2009-09-01',112) and convert(varchar(7),'2010-01-31',112)我用这样子的代码查但是出来的结果是这样的,怎么回事呢? (少了9月的数据咯)2009 10 30
2009 11 40
2009 12 50
2010 1 60
用我9楼的代码
select convert(varchar(6),date,112) '日期',fqty from
(
select cast(convert(varchar(10),ltrim(fyear)+'-'+ltrim(fmonth)+'-01',120) as datetime) as date,fqty from #t
)
tt
where date between '2009-9-01' and '2010-01-01'
日期 fqty
------ -----------
200909 20
200910 30
200911 40
200912 50
201001 60(5 行受影响)
字符串的比较 9比10、11肯定大了
都转换为datetime类型比较
select * from #t
where convert(datetime,ltrim(fyear)+'-'++ltrim(fmonth)+'-01',112)
between convert(datetime,'2009-09-01',112)
and convert(datetime,'2010-01-31',112)
where ltrim(fyear)+right('0'+ltrim(fmonth),2)
between convert(varchar(6),cast('2009-09-01' as datetime),112) and convert(varchar(6),cast('2010-01-31' as datetime),112)