在sql2005数据库中有一表数据如下:
column1 column2 column3 column4
aaa 111 2009-7-1 2009-8-1
bbb xxx 2009-9-1 2009-11-30
ccc ddd 2009-12-1 null(当前)
现有一查询条件 2009-10
我想查出 column3<=2009-10<=column4的数据 ,
如果是2009-10,那么
bbb xxx 2009-9-1 2009-11-30 满足条件 像这样的SQL要怎么写?
column1 column2 column3 column4
aaa 111 2009-7-1 2009-8-1
bbb xxx 2009-9-1 2009-11-30
ccc ddd 2009-12-1 null(当前)
现有一查询条件 2009-10
我想查出 column3<=2009-10<=column4的数据 ,
如果是2009-10,那么
bbb xxx 2009-9-1 2009-11-30 满足条件 像这样的SQL要怎么写?
解决方案 »
- vb连sql server2005的问题,救命呀
- 在sqlserver中求一SQL
- 求资料表结合SQL语法
- dawugui 你马上就两颗钻石啦,你目前薪情如何啊
- 关于行列转换的
- 请问如何在存储过程里调用其他数据库里面的表的内容?多谢
- [求助]MS SQL执行CHECKTABLE扫出来的错误解决
- SQL 2005 占用内存过多,执行效率很低 急救
- 菜鸟问题:我有一个SQL SERVER的数据库,我把它的两个文件拷了过来,yyt_data.mdf和yyt_log,但是我拿到我这儿就打不开了。
- 技巧性的问题?
- 报料:关于CAST(DECIMAL(9,2) AS BINARY(8))的转换方式。
- 用数据库软件比如sql server可以查看信息管理系统的数据库表吗?
WHERE column3 <= '2009-10-31 23:59:59'
AND column4 >= '2009-10-01'
from tb
where datediff(month,isnull(column3,getdate()),'2009-10-01')>=0
and datediff(month,'2009-10-01',isnull(column4,getdate()))>=0
insert into tb values('aaa' , '111' , '2009-7-1' , '2009-8-1')
insert into tb values('bbb' , 'xxx' , '2009-9-1' , '2009-11-30')
insert into tb values('ccc' , 'ddd' , '2009-12-1', null)
goselect * from tb where '2009-10' between convert(varchar(7),column3,120) and convert(varchar(7),isnull(column4,getdate()),120) drop table tb/*
column1 column2 column3 column4
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
bbb xxx 2009-09-01 00:00:00.000 2009-11-30 00:00:00.000(所影响的行数为 1 行)
*/
set @time='2009-10'
select
*
from
tb
where
convert(varchar(7),column3,120)<=@time and @time<=convert(varchar(7),column4,120)
insert into tb values('aaa' , '111' , '2009-7-1' , '2009-8-1')
insert into tb values('bbb' , 'xxx' , '2009-9-1' , '2009-11-30')
insert into tb values('ccc' , 'ddd' , '2009-12-1', null)
godeclare @dt as varchar(7)set @dt = '2009-10'
select * from tb where @dt between convert(varchar(7),column3,120) and convert(varchar(7),isnull(column4,getdate()),120)
/*
column1 column2 column3 column4
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
bbb xxx 2009-09-01 00:00:00.000 2009-11-30 00:00:00.000(所影响的行数为 1 行)
*/set @dt = '2009-12'
select * from tb where @dt between convert(varchar(7),column3,120) and convert(varchar(7),isnull(column4,getdate()),120)
/*
column1 column2 column3 column4
---------- ---------- ------------------------------------------------------ ------------------------------------------------------
ccc ddd 2009-12-01 00:00:00.000 NULL(所影响的行数为 1 行)
*/drop table tb
insert into tb values('aaa','111','2009-7-1','2009-8-1')
insert into tb values('bbb','xxx','2009-9-1','2009-11-30')
insert into tb values('ccc','ddd','2009-12-1',null)
--10月
select *
from tb
where datediff(month,isnull(column3,getdate()),'2009-10-01')>=0
and datediff(month,'2009-10-01',isnull(column4,getdate()))>=0column1 column2 column3 column4
---------- ---------- ---------- ----------
bbb xxx 2009-9-1 2009-11-30(1 行受影响)
--12月
select *
from tb
where datediff(month,isnull(column3,getdate()),'2009-12-01')>=0
and datediff(month,'2009-12-01',isnull(column4,getdate()))>=0
column1 column2 column3 column4
---------- ---------- ---------- ----------
ccc ddd 2009-12-1 NULL(1 行受影响)