再求高手SQL语句 - 编程论坛

#2

红色警戒2010-09-20 16:20

把时间格式化成和数据库中格式相同的字符串，然后查询
select * form tablename where date >= strdate1 and time > strtime1 and date <= strdate2 and time <= strtime2

#3

aei1352010-09-20 18:07

应该是20100910吧?

#4

zhaoyang10082010-09-20 20:19

把date格式化为9-10-2010，time格式化为13：00，依此类推然后执行这个查询
select *
from Table
where (CAST(date as datetime) between '9-10-2010' and '9-11-2010') and (CAST(time as datetime) between '13:00' AND '21:00')
就可以了
其中cast函数是改变数据类型的，把char类型改为了datetime类型

#5

yc6349980902010-09-20 22:55

我是个菜鸟，如果不对请各位高手见谅。
我觉得可以把date和time字段合并起来再求。代码如下：
select rtrim(date)+ltrim(time) as dt into #t from t2
select dt from #t where dt>=201009101300 and dt<=201009112300
drop table #t

#6

wangwenting22010-09-20 23:01

菜鸟来访
CREATE TABLE TimeTest
(
   date VARCHAR(10),
   [time] VARCHAR(10),
   DATA VARCHAR(10)
)
go
INSERT INTO TimeTest
SELECT '2010910','0900','23'

UNION SELECT '2010910','1300','21'
UNION SELECT '2010910','2200','29'
UNION SELECT '2010911','1300 ','19'
UNION SELECT '2010911','2200','27'
UNION SELECT '2010912','1300','22'
UNION SELECT '2010912','1600','18'

SELECT * FROM TimeTest
WHERE
CONVERT(DATETIME,LEFT(date,4)+'-'+LEFT(RIGHT(date,4),2)+'-'+RIGHT(date,2)+' '+ LEFT([TIME],2)+':'+RIGHT([TIME],2) )
BETWEEN '2010-9-10 13:00:00' and  '2010-9-11 23:00:00'

#7

btcj2010-09-21 15:13

感谢6楼的，经查询正确，
2楼和4楼都忽略了时间TIME每天都是重复的24个值，所以用and连接并不能查询到所要的结果。