~~~~~~~~~~~~~~~~~~求一个SQL语句,关于时间!

[yaochutao]

求一个时间范围内的记录(2008-01-01到2008-04-01之间的数据,头和尾都要)

表名:table
字段:ID,time
ID time
1 2008-01-01 12:30:10
2 2008-02-01 08:30:10
3 2008-03-01 20:30:10
4 2008-04-01 12:30:10
4 2008-06-01 01:30:10
4 2008-07-01 12:30:10

我想查2008-01-01到2008-04-01
select * from table where time between 2008-01-01 and 2008-04-01
seelct * from table where time >=2008-01-01 and time <= 2008-04-01
这样查处的结果是查不到下面两条记录
2008-01-01 12:30:10
2008-04-01 12:30:10
我想可能是数据库 2008-04-01当作是2008-04-01 00:00:00,所以第二个条件time <= 2008-04-01 00:00:00是查不到2008-04-01 12:30:10这条记录的!!


大家帮帮忙!

[xiaoqhuang]

select * from table where time between '2008-01-01' and DateAdd(day,1,'2008-04-01')

[xiaoqhuang]

你是对的, 所有要把结束日期+1天

[hsabout]

select * from table where datediff(day,time,'2008-01-01')<=0 and datediff(day,time,'2008-04-01')>=0

[yaochutao]

如果用户输入的是 2008-04-01 12:12:12 而不是2008-04-01 在加一天会不会多一天

[xxoo2007]

select * from table
where time between convert(datetime,'2008-01-01 00:00:00')
and convert(datetime,'2008-04-01 23:59:59')

[suyiming]

select * from table where time between '2008-01-01' and DateAdd(day,1,'2008-04-01')

--注释：例如想找2008-8-8 15:08:85 159之前的记录，那将时间加多一天

[辉之不趣]

[Quote=引用 5 楼 xxoo2007 的回复:]
select * from table
where time between convert(datetime,'2008-01-01 00:00:00')
and convert(datetime,'2008-04-01 23:59:59')

[/Quote]

[yaochutao]

但是 time的类型是nvarchar(19)的!!
我试了下,日期+1报错

服务器: 消息 241，级别 16，状态 1，行 1
从字符串转换为 datetime 时发生语法错误。

[niuniuhuang]

加一天

[conannb]

select * from table
where time >='2008-01-01 00:00:00'
and time <= '2008-04-01 23:59:59'

[潜龙无用]

seelct * from table where time >='2008-01-01' and time < '2008-04-02'

[潜龙无用]

字符串转日期可以用cast或convert

[conan304]

declare @t table(id int,time datetime)
insert @t select 1,'2008-01-01 12:30:10'
union all select 2,'2008-02-01 08:30:10' 
union all select 3,'2008-03-01 20:30:10' 
union all select 4,'2008-04-01 12:30:10' 
union all select 4,'2008-06-01 01:30:10' 
union all select 4,'2008-07-01 12:30:10'

select * from @t
where [time]>='2008-1-1' and [time]<='2008-4-1 23:59:59'

/*

（所影响的行数为 6 行）

id          time                                                   
----------- ------------------------------------------------------ 
1           2008-01-01 12:30:10.000
2           2008-02-01 08:30:10.000
3           2008-03-01 20:30:10.000
4           2008-04-01 12:30:10.000

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

[hztltgg]

为了好看，还可以把0分0秒也加上

where [time]>='2008-1-1 0:0:0' and [time]<='2008-4-1 23:59:59'

[大巧不工]

select * from table where to_date(to_char(time,'yyyy-mm-dd')) > = to_date('2008-01-01') and to_date(to_char(time,'yyyy-mm-dd')) <= to_date('2008-04-01')

[yaochutao]

好了,我知道了,,原来是我的time 里面有个不是日期类型的数据,,不过还是谢谢你们,,学习到了!!

[yaochutao]

好了,我知道了,,原来是我的time 里面有个不是日期类型的数据,,不过还是谢谢你们,,学习到了!!