select * from tab1 where datediff(m,字段1,getdate())<>0
select top 8000 identity(int,0,1) as id into # tmp from syscolumns a , syscolumns bselect convert(varchar(7),dateadd(month , tmp.id , 字段1+'01'),120) from tb , tmp where convert(varchar(7),dateadd(month , tmp.id , 字段1+'01'),120) <= convert(varchar(7),getdate(),120)
select * from tab1 where datediff(m,字段1,getdate())>=0
写错应该是<=select * from tab1 where 字段1 <= '2008-05-20'
请教.求某一日期到到现在的所有月份 select * from tab1 where datediff(m,case(字段1 as datetime),getdate())>=0 and case(字段1 as datetime)>case('某一日期' as datetime)
select * from tab1 where 字段1 <= convert(char(7),getdate(),120)
create table tb(字段1 varchar(7)) insert into tb values('2008-01') insert into tb values('2008-02') insert into tb values('2008-03') goselect top 8000 identity(int,0,1) as id into tmp from syscolumns a , syscolumns bselect distinct convert(varchar(7),dateadd(month , tmp.id , 字段1 + '-01'),120) 字段1 from tb , tmp where convert(varchar(7),dateadd(month , tmp.id , 字段1+'-01'),120) <= convert(varchar(7),getdate(),120) order by 字段1drop table tb,tmp/* 字段1 ------- 2008-01 2008-02 2008-03 2008-04 2008-05(所影响的行数为 5 行)*/
请教.求某一日期到到现在的所有月份 select * from tab1 where 字段1 <= convert(char(7),getdate(),120) and 字段1 >= convert(char(7),某一日期,120)
select dt = base.date+addi.n from (select date = cast('2008-1-1' as datetime)) as base, -- 基准日期 ( select n = n2.n*100+n1.n*10+n0.n from (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n2, (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n1, (select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n0 ) as addi -- 一千天增量 where base.date+addi.n<getdate() order by dtdt 2008-01-01 00:00:00.000 2008-01-02 00:00:00.000 ... 2008-05-19 00:00:00.000 2008-05-20 00:00:00.000 2008-05-21 00:00:00.000(所影响的行数为 142 行)
--如果你是如下的数据就简单了。 create table tb(字段1 varchar(7)) insert into tb values('2008-01') insert into tb values('2008-02') insert into tb values('2008-03') insert into tb values('2008-04') insert into tb values('2008-05') goselect 字段1 from tb where 字段1 <= convert(varchar(7),getdate(),120) order by 字段1drop table tb/* 字段1 ------- 2008-01 2008-02 2008-03 2008-04 2008-05(所影响的行数为 5 行)*/
不需要,关闭sql后临时表自动死亡.
select * from tab1 where datediff(m,字段1,getdate())<>0 该语句有用,我顶
select * from tab1 where datediff(m,字段1,getdate())>=0
datediff(m,case(字段1 as datetime),getdate())>=0
and case(字段1 as datetime)>case('某一日期' as datetime)
insert into tb values('2008-01')
insert into tb values('2008-02')
insert into tb values('2008-03')
goselect top 8000 identity(int,0,1) as id into tmp from syscolumns a , syscolumns bselect distinct convert(varchar(7),dateadd(month , tmp.id , 字段1 + '-01'),120) 字段1
from tb , tmp
where convert(varchar(7),dateadd(month , tmp.id , 字段1+'-01'),120) <= convert(varchar(7),getdate(),120)
order by 字段1drop table tb,tmp/*
字段1
-------
2008-01
2008-02
2008-03
2008-04
2008-05(所影响的行数为 5 行)*/
and 字段1 >= convert(char(7),某一日期,120)
from (select date = cast('2008-1-1' as datetime)) as base, -- 基准日期
(
select n = n2.n*100+n1.n*10+n0.n
from
(select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n2,
(select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n1,
(select 0 as n union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) n0
) as addi -- 一千天增量
where base.date+addi.n<getdate()
order by dtdt
2008-01-01 00:00:00.000
2008-01-02 00:00:00.000
...
2008-05-19 00:00:00.000
2008-05-20 00:00:00.000
2008-05-21 00:00:00.000(所影响的行数为 142 行)
create table tb(字段1 varchar(7))
insert into tb values('2008-01')
insert into tb values('2008-02')
insert into tb values('2008-03')
insert into tb values('2008-04')
insert into tb values('2008-05')
goselect 字段1 from tb where 字段1 <= convert(varchar(7),getdate(),120) order by 字段1drop table tb/*
字段1
-------
2008-01
2008-02
2008-03
2008-04
2008-05(所影响的行数为 5 行)*/
该语句有用,我顶