如何求出两个日期之间的所有记录？

[romeo]

a表有两个字段是：in_time,out_time,我想求出start='2004-4-6'到end='2004-5-8'之间的所有数据（start<in_time <end && start<out_time<end），我应该怎么写sql语句啊？

[zjcxc]

楼主客气了

[leeboyan]

select * from tablename where in_time>'2004-4-6' and in_time<'2004-5-8' and out_time>'2004-4-6' and out_time<'2004-5-8'

[zjcxc]

select * from 表
where in_time between '2004-4-6' and '2004-5-8'
and out_time between '2004-4-6' and '2004-5-8'

[magnetmoon]

x between a and b 和 x>a and x<b 或 x>=a and x<=b 是不一樣

select * from 表
where in_time>='2004-4-6' and in_time<='2004-5-8'
and out_time>='2004-4-6' and out_time<='2004-5-8'

[romeo]

啊？原来可以和字符串直接比较啊？晕～～～～～～～
揭帖给分！！

[wall86144]

between

[SilverSands]

select * from 表
where in_time>='2004-4-6' and in_time<='2004-5-8'
and out_time>='2004-4-6' and out_time<='2004-5-8'
在sql server是可以直接比较的

[massice]

Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "mydata","sa",""
y1=Request.Form("year1") 'y1,y2,m1,m2,d1,d2都是从前一页面而来
y2=Request.Form("year2")
m1=Request.Form("month1")
m2=Request.Form("month2")
d1=Request.Form("day1")
d2=Request.Form("day2")
temp1=y1&"-"&m1&"-"&d1
temp2=y2&"-"&m2&"-"&d2
temp1=FormatDateTime(temp1) ‘把temp1,temp2的格式转换成DateTime类型
temp2=FormatDateTime(temp2)
s="select * from worktime where LoginID='"&session("loginid")&"' and Workdate between "&temp1&" and "&temp2
Set rs=conn.Execute(s)
session("loginid")中保存着wang
我想查询2004-4-1到2004-5-1的wang的记录，请问上边的rs执行结果是否正确？？
我的执行结果为什么rs是NULL，而表中有wang的记录啊？？怎么回事？
还请高手指点一下
WORKTIME表
ID NAME LOGINID WORKTIME WORKDATE LEAVE
1 wang wang 7:58:53 2004-4-10 空
3 wang wang 8:00:00 2004-4-11 事假
7 li li 7:30:14 2004-4-15 病假
12 wang wang 7:45:00 2004-4-16 病假

[zjcxc]

Set conn=Server.CreateObject("ADODB.Connection")
conn.Open "mydata","sa",""
y1=Request.Form("year1") 'y1,y2,m1,m2,d1,d2都是从前一页面而来
y2=Request.Form("year2")
m1=Request.Form("month1")
m2=Request.Form("month2")
d1=Request.Form("day1")
d2=Request.Form("day2")
temp1=y1&"-"&m1&"-"&d1
temp2=y2&"-"&m2&"-"&d2
temp1=FormatDateTime(temp1) ‘把temp1,temp2的格式转换成DateTime类型
temp2=FormatDateTime(temp2)
s="select * from worktime where LoginID='"&session("loginid")&"' and Workdate between '"&temp1&"' and '"&temp2 & "'"
Set rs=conn.Execute(s)

[zjcxc]

即:
s="select * from worktime where LoginID='"&session("loginid")&"' and Workdate between "&temp1&" and "&temp2

改为:
s="select * from worktime where LoginID='"&session("loginid")&"' and Workdate between '"&temp1&"' and '"&temp2 & "'" --你的少了单引号

[massice]

十分感谢zjcxc邹建的回复！！
谢谢