declare @s1 datetime ,@s2 datetime set @s1='2009-12-03' set @s2='2009-12-05' select * from [TB] where 开始日期<=@s1 and 结束日期>=@s2??
create table tb(订单号 varchar(10) , 开始日期 datetime , 结束日期 datetime) insert into tb values('订单A','2009-12-03','2009-12-05') insert into tb values('订单B','2009-12-01','2009-12-07') godeclare @sdate datetime declare @edate datetime set @sdate = '2009-12-03' set @edate = '2009-12-05' select distinct m.* from tb m, (--获取各时间的子查询 select dateadd(dd,num,@sdate) dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,@sdate)<=@edate ) n where n.dt between m.开始日期 and m.结束日期drop table tb /* 订单号 开始日期 结束日期 ---------- ------------------------------------------------------ ------------------------------------------------------ 订单A 2009-12-03 00:00:00.000 2009-12-05 00:00:00.000 订单B 2009-12-01 00:00:00.000 2009-12-07 00:00:00.000(所影响的行数为 2 行) */
declare @startdt datetime declare @enddt datetime select @startdt='2009-12-03',@enddt='2009-12-05' select * from tb where 开始日期 between @startdt and @enddt or 结束日期 between @startdt and @enddt or @startdt between 开始日期 and 结束日期 or @enddt between 开始日期 and 结束日期
--如果你不想定义变量,就带入具体的值进行查询. create table tb(订单号 varchar(10) , 开始日期 datetime , 结束日期 datetime) insert into tb values('订单A','2009-12-03','2009-12-05') insert into tb values('订单B','2009-12-01','2009-12-07') goselect distinct m.* from tb m, (--获取各时间的子查询 select dateadd(dd,num,'2009-12-03') dt from (select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a where dateadd(dd,num,'2009-12-03')<='2009-12-05' ) n where n.dt between m.开始日期 and m.结束日期drop table tb /* 订单号 开始日期 结束日期 ---------- ------------------------------------------------------ ------------------------------------------------------ 订单A 2009-12-03 00:00:00.000 2009-12-05 00:00:00.000 订单B 2009-12-01 00:00:00.000 2009-12-07 00:00:00.000(所影响的行数为 2 行) */
是生产排程的一个查询,要做甘特图。。就是输入一个日期时间段,将时间段内所有正在生产的订单统计出来。比如三个订单 订单A,2009-12-03,2009-12-05 订单B,2009-12-01,2009-12-07 订单C,2009-12-04,2010-01-09我如果输入2009-12-01至2009-12-31.我的图上要显示所有本月进行的订单。 但是当前的语句设置为 结束日期>2009-12-01 and <2009-12-31 的话 订单c是不会出来的。但是它确实是在生产当中的
dawugui 大哥真牛B.初步测试完全满足我期望的效果
开始日期 between '2009-12-03' and '2009-12-05'or 结束日期 between '2009-12-03' and '2009-12-05'
开始日期 between '2009-12-03' and '2009-12-05'or 结束日期 between '2009-12-03' and '2009-12-05'
set @s1='2009-12-03'
set @s2='2009-12-05'
select * from [TB] where 开始日期<=@s1 and 结束日期>=@s2??
insert into tb values('订单A','2009-12-03','2009-12-05')
insert into tb values('订单B','2009-12-01','2009-12-07')
godeclare @sdate datetime
declare @edate datetime
set @sdate = '2009-12-03'
set @edate = '2009-12-05'
select distinct m.* from tb m,
(--获取各时间的子查询
select
dateadd(dd,num,@sdate) dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,@sdate)<=@edate
) n
where n.dt between m.开始日期 and m.结束日期drop table tb /*
订单号 开始日期 结束日期
---------- ------------------------------------------------------ ------------------------------------------------------
订单A 2009-12-03 00:00:00.000 2009-12-05 00:00:00.000
订单B 2009-12-01 00:00:00.000 2009-12-07 00:00:00.000(所影响的行数为 2 行)
*/
declare @enddt datetime
select @startdt='2009-12-03',@enddt='2009-12-05'
select * from tb
where 开始日期 between @startdt and @enddt
or 结束日期 between @startdt and @enddt
or @startdt between 开始日期 and 结束日期
or @enddt between 开始日期 and 结束日期
create table tb(订单号 varchar(10) , 开始日期 datetime , 结束日期 datetime)
insert into tb values('订单A','2009-12-03','2009-12-05')
insert into tb values('订单B','2009-12-01','2009-12-07')
goselect distinct m.* from tb m,
(--获取各时间的子查询
select
dateadd(dd,num,'2009-12-03') dt
from
(select isnull((select count(1) from sysobjects where id<t.id),0) as num from sysobjects t) a
where
dateadd(dd,num,'2009-12-03')<='2009-12-05'
) n
where n.dt between m.开始日期 and m.结束日期drop table tb /*
订单号 开始日期 结束日期
---------- ------------------------------------------------------ ------------------------------------------------------
订单A 2009-12-03 00:00:00.000 2009-12-05 00:00:00.000
订单B 2009-12-01 00:00:00.000 2009-12-07 00:00:00.000(所影响的行数为 2 行)
*/
订单A,2009-12-03,2009-12-05
订单B,2009-12-01,2009-12-07
订单C,2009-12-04,2010-01-09我如果输入2009-12-01至2009-12-31.我的图上要显示所有本月进行的订单。
但是当前的语句设置为 结束日期>2009-12-01 and <2009-12-31 的话 订单c是不会出来的。但是它确实是在生产当中的
结束日期 between '2009-12-03' and '2009-12-05'
结束日期 between '2009-12-03' and '2009-12-05'