求一个时间范围内的记录(2008-01-01到2008-04-01之间的数据,头和尾都要) 表名:table
字段:ID,time(time字段是nvarchar类型的)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 DateAdd(day,1,'2008-04-01')但是 time的类型是nvarchar(19)的!!
我试了下,日期+1报错 服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。大家帮帮忙!
字段:ID,time(time字段是nvarchar类型的)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 DateAdd(day,1,'2008-04-01')但是 time的类型是nvarchar(19)的!!
我试了下,日期+1报错 服务器: 消息 241,级别 16,状态 1,行 1
从字符串转换为 datetime 时发生语法错误。大家帮帮忙!
select * from table where
datediff(dd,'2008-01-01',time)>=0
datediff(dd,time,'2008-04-01')>=0
--掉了个and
select * from @s where
datediff(dd,'2008-01-01',time)>=0 and
datediff(dd,time,'2008-04-01')>=0
set @date = '2008-01-01 12:30:10'
print @dateselect convert(datetime,@date)select * from table where convert(datetime,time) between 2008-01-01' and DateAdd(day,1,'2008-04-01')
--并没有报错啊:
--> 测试数据: @s
declare @s table (ID int,time nvarchar(19))
insert into @s
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 @s where
time between '2008-01-01' and dateadd(dd,1,'2008-04-01')
insert into tb select 1,'2008-01-01 12:30:10'
insert into tb select 2,'2008-02-01 08:30:10'
insert into tb select 3,'2008-03-01 20:30:10'
insert into tb select 4,'2008-04-01 12:30:10'
insert into tb select 4,'2008-06-01 01:30:10'
insert into tb select 4,'2008-07-01 12:30:10'
insert into tb select 5,'4'select * from tb where date
between '2008-01-01' and DateAdd(day,1,'2008-04-01') 消息 241,级别 16,状态 1,第 10 行
从字符串向 datetime 转换时失败。有非时间类型的数据
create table tb(id int,[time] nvarchar(19))insert into tb select 1 , '2008-01-01 12:30:10'
insert into tb select 2 , '2008-02-01 08:30:10'
insert into tb select 3 , '2008-03-01 20:30:10'
insert into tb select 4 , '2008-04-01 12:30:10'
insert into tb select 4 , '2008-06-01 01:30:10'
insert into tb select 4 , '2008-07-01 12:30:10' select * from tb where [time] between '2008-01-01' and DateAdd(day,1,'2008-04-01') /*
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
*/drop table tb
select * from table where time between '2008-01-01' and DateAdd(day,1,'2008-04-01') 中日期字符串没有问题就可以.如:
select dateadd(day,1,'2007-1-1')
但你如果输入的日期字符串不正确,那就会报错误.
如:
select dateadd(day,1,'2007-2-29')