例如表A:,其中有字段DATATMP,TIMETMP,等等//---------------------------------------------
DATATMP TIMETMP 其它字段
------------------------------------------------
2009-1-1 10:00:00 xxxx
2009-3-1 10:05:00 xxxx
2007-3-2 7:11:00 xxxx
2009-3-2 11:21:00 xxxx
2007-3-2 8:11:00 xxxx
2007-3-3 9:52:00 xxxx
2007-3-3 13:00:00 xxxx
------------------------------------------------
我想获取2009-3-2 号至 2009-3-3的记录,可以这么写:
select *form A where DATATMP>='2009-3-2' and DATATMP<='2009-3-3'但是,如果我想获取某一个日期/时间段的记录该怎么做?
例:我想获取2009-3-2 号8:11:00 到 2007-3-3 号 13:00:00 的记录?
DATATMP TIMETMP 其它字段
------------------------------------------------
2009-1-1 10:00:00 xxxx
2009-3-1 10:05:00 xxxx
2007-3-2 7:11:00 xxxx
2009-3-2 11:21:00 xxxx
2007-3-2 8:11:00 xxxx
2007-3-3 9:52:00 xxxx
2007-3-3 13:00:00 xxxx
------------------------------------------------
我想获取2009-3-2 号至 2009-3-3的记录,可以这么写:
select *form A where DATATMP>='2009-3-2' and DATATMP<='2009-3-3'但是,如果我想获取某一个日期/时间段的记录该怎么做?
例:我想获取2009-3-2 号8:11:00 到 2007-3-3 号 13:00:00 的记录?
from tb
where cast(datatmp+' '+timetmp as datetime) between '2009-3-2 8:11:00' and '2007-3-3 13:00:00'
go
create table [tb]([DATATMP] varchar(10),[TIMETMP] varchar(10),[其它字段] varchar(10))
insert [tb] select '2009-1-1','10:00:00','xxxx'
union all select '2009-3-1','10:05:00','xxxx'
union all select '2007-3-2','7:11:00','xxxx'
union all select '2009-3-2','11:21:00','xxxx'
union all select '2007-3-2','8:11:00','xxxx'
union all select '2007-3-3','9:52:00','xxxx'
union all select '2007-3-3','13:00:00','xxxx'
goselect *
from tb
where cast(DATATMP as datetime)+cast([TIMETMP] as datetime)
between '2007-3-3 13:00:00' and '2009-03-02 8:11:00'
/*
DATATMP TIMETMP 其它字段
---------- ---------- ----------
2009-1-1 10:00:00 xxxx
2009-3-1 10:05:00 xxxx
2007-3-3 13:00:00 xxxx(3 行受影响)
*/
from tb
where cast(DATATMP as datetime)+cast([TIMETMP] as datetime)
between '2007-3-3 13:00:00' and '2009-03-02 8:11:00'可是结果集中怎么不显示这两条记录呢,我想叫他们也显示。
2007-3-3 13:00:00
2009-03-02 8:11:00
insert #tb1 select '2009-1-1','10:00:00','xxxx'
union all select '2009-3-1','10:05:00','xxxx'
union all select '2007-3-2','7:11:00','xxxx'
union all select '2009-3-2','11:21:00','xxxx'
union all select '2007-3-2','8:11:00','xxxx'
union all select '2007-3-3','9:52:00','xxxx'
union all select '2007-3-3','13:00:00','xxxx'
select * from #tb1 where cast(DATATMP as datetime)+cast([TIMETMP] as datetime)
between '2007-3-3 13:00:00' and '2009-03-02 8:11:00'DATATMP TIMETMP 其它字段
---------- ---------- ----------
2009-1-1 10:00:00 xxxx
2009-3-1 10:05:00 xxxx
2007-3-3 13:00:00 xxxx(3 行受影响)
如果用在access数据库这样写能行吗?
select * from A where DATATMP>=#2009-3-2 08:11:00# and DATATMP <=#2009-3-3 13:00:00# 当然某些情况下你也可以用CDATE()函数将 '2009-3-2 08:11:00' 转换成日期。QQ群 48866293 / 12035577 / 7440532 / 13666209
ACCESS专业论坛
http://www.accessbbs.cn/bbs/index.php .
http://www.office-cn.net/vvb/ .
http://www.accessoft.com/bbs/index.asp .
http://www.access-programmers.co.uk/forums .
.
http://www.office-cn.net/home/space.php?uid=141646 .
where (datetmp between '2009-3-2' and '2007-3-3')
and (timetmp between '8:11:00' and '13:00:00')