按日期查询记录的语句怎么写？

[miaomiao521]

我要查表[booking]中的[date]字段中日期值为昨天10:00到今日10:00之间的记录~
date中的记录格式为2004-1-10 12:43:22~

谢谢各位大虾咯~

[oklsl]

select * from booking where date between to_date('2004-01-10 12:43:22','yyyy-mm-dd hh24:mi:ss') and to_date('2004-01-11 12:43:22','yyyy-mm-dd hh24:mi:ss')

[leeboyan]

select * from booking where date>=convert(varchar,dateadd(day,-1,getdate(),112)+'10:00:00' and date<=convert(varchar,getdate(),112)+'10:00:00'

[laughsmile]

select * from booking
where DATEDIFF (hour,cast(convert(char(10),getdate()-1,120)+' 10:00:00' as datetime),[date]) <12 and DATEDIFF ( hour , cast(convert(char(10),getdate()-1,120)+' 10:00:00' as datetime),[date]) >0

[laughsmile]

嗯，相比楼上的各位来说写的有点麻烦,just do exercise.
不过在sqlserver2000中怎么好象没有to_date()

[zjcxc]

--这样简单一点吧?
select * from booking where datediff(day,dateadd(hour,-10,[date]),getdate())=1

[zjcxc]

--下面是测试

--测试数据
declare @t table(dt datetime)
insert into @t
select '2004-01-13 12:01'
union all select '2004-01-13 09:01'
union all select '2004-01-13 10:30'
union all select '2004-01-13 10:01'
union all select '2004-01-12 12:01'
union all select '2004-01-12 11:01'
union all select '2004-01-12 10:00'
union all select '2004-01-12 09:01'
union all select '2004-01-12 08:01'

--查询
select * from @t
where datediff(day,dateadd(hour,-10,dt),getdate())=1

/*--测试结果
dt
------------------------------------------------------
2004-01-13 09:01:00.000
2004-01-12 12:01:00.000
2004-01-12 11:01:00.000
2004-01-12 10:00:00.000

（所影响的行数为 4 行）
--*/

[leeboyan]

刚才有点错误，现在测了一下：
表booking
date
2003-11-05 12:00:00.000
2004-01-12 14:00:00.000
2004-01-12 15:00:00.000
2005-01-01 07:00:00.000
-----------------------------
sql语句：
select * from booking
where date between convert(varchar(10),dateadd(day,-1,getdate()),120)+' 10:00:00' and convert(varchar(10),getdate(),120)+' 10:00:00'
-----------结果-------------------
date
2004-01-12 14:00:00.000
2004-01-12 15:00:00.000
----------------------------------
（所影响的行数为 2 行）

[miaomiao521]

哇哇哇~
不用那么麻烦的~
偶是用在asp中~能用就行~

[zjcxc]

--那就这个啦,够简单了吧.
select * from booking where datediff(day,dateadd(hour,-10,[date]),getdate())=1

[MaDaHai]

up

[miaomiao521]

Microsoft JET Database Engine 错误 '80040e14'

表达式中 'getdate' 函数未定义。

/admin/NewBook.asp，行 79
------------
出错拉~

[zjcxc]

你是怎么写的,将你写的代码贴出来.

[miaomiao521]

sql = "select * from [booking] where 用户名='"& UserNow &"' AND datediff(day,dateadd(hour,-10,[预约确认时间]),getdate())=1 order by 编号 desc"

rs.open sql,conn,1,1'79行~

[zjcxc]

晕,你的数据库不是sql数据库?

[miaomiao521]

很大区别么？
哭~

[zjcxc]

sql = "select * from [booking] where 用户名='"& UserNow &"' AND datediff("d",dateadd("h",-10,[预约确认时间]),getdate())=1 order by 编号 desc"

[zjcxc]

'ACCESS用
sql = "select * from [booking] where 用户名='"& UserNow &"' AND datediff("d",dateadd("h",-10,[预约确认时间]),date())=1 order by 编号 desc"

[sunrisehy2003]

UP

[sandychen]

這樣實現日期格式比較正式通用:
select * from booking where cast([date] as varchar(19))
between convert(varchar(10),getdate()-1,120)+' 10:00:00'
and convert(varchar(10),getdate(),120)+' 10:00:00'

[miaomiao521]

Dim H, D1, D2, SQL
H = Hour(Now)
D1= dateadd("d", -1, date) & " 10:00:00"
if H < 10 then
D2 = Now
else
D2 = Date & " 10:00:00"
end if

SQL = "select * from [booking] where 预约确认时间 between #"+ D1 +"# and #"+ D2 +"#"

response.write SQL
rs.open SQL,conn,1,3

输出结果：

select * from [booking] where 预约确认时间 between #2004-2-2 10:00:00# and #2004-2-3 10:00:00#
Microsoft JET Database Engine 错误 '80040e10'

至少一个参数没有被指定值。

/blt/admin/NewBook.asp，行 46

======================
怎么换SQL语句它还是'80040e10' 这个错误，至少一个参数没有被指定~
我想可能不是SQL语句的问题，使我IIS哪里没有设置好？还是ACCESS数据库哪里没有设置好？