SQL 时间查询

Question

表名： date
cbstartdate                 cbenddate2010-03-16 12:00:00.000 2010-05-17 17:45:23.000
2010-03-17 12:00:00.000 2010-05-18 23:45:55.000
2010-04-12 12:00:00.000 2010-05-18 23:44:06.000
2010-05-12 12:00:00.000 2010-05-18 23:46:05.000
2010-05-12 12:00:00.000 2010-05-18 23:46:05.000
2010-05-17 17:54:50.560 2010-05-18 23:03:40.000
2010-05-17 17:54:50.560 2010-05-18 23:03:40.000
cbstartdate 起始时间  cbenddate 结束时间
我想要查询 时间在cbstartdate 在2010-05-17  到 cbenddate 2010-05-18 之间的数据

Answer 1

SELECT * FROM date where cbstartdate>'2010-05-17' and cbenddate<'2010-05-19'

Answer 2

select * from [Table] 
where convert(varchar(10),cbstartdate,120)='2010-05-17'
and  convert(varchar(10),cbenddate,120)='2010-05-18'

Answer 3

--> 生成测试数据表: [date]
IF OBJECT_ID('[date]') IS NOT NULL
DROP TABLE [date]
GO
CREATE TABLE [date] ([cbstartdate] [datetime],[cbenddate] [datetime])
INSERT INTO [date]
SELECT '2010-03-16 12:00:00.000','2010-05-17 17:45:23.000' UNION ALL
SELECT '2010-03-17 12:00:00.000','2010-05-18 23:45:55.000' UNION ALL
SELECT '2010-04-12 12:00:00.000','2010-05-18 23:44:06.000' UNION ALL
SELECT '2010-05-12 12:00:00.000','2010-05-18 23:46:05.000' UNION ALL
SELECT '2010-05-12 12:00:00.000','2010-05-18 23:46:05.000' UNION ALL
SELECT '2010-05-17 17:54:50.560','2010-05-18 23:03:40.000' UNION ALL
SELECT '2010-05-17 17:54:50.560','2010-05-18 23:03:40.000'
-->SQL查询如下:
declare @st datetime,@et datetime
select @st='2010-05-17',@et= '2010-05-18' 
SELECT * FROM [date] WHERE [cbstartdate]>=@st AND [cbenddate]< DATEADD(DD,1,@et)
/*
cbstartdate             cbenddate
----------------------- -----------------------
2010-05-17 17:54:50.560 2010-05-18 23:03:40.000
2010-05-17 17:54:50.560 2010-05-18 23:03:40.000(2 行受影响)
*/?

Answer 4

SELECT * FROM date where 
cbstartdate>='2010-05-17' and cbstartdate<dateadd(day,1,cast('2010-05-18' as datetime)

Answer 5

时间在cbstartdate 在2010-05-17 到 cbenddate 2010-05-18 之间的数据?这需求提得实在太怪,先顶.

Answer 6

--这样吗?
select * from tb
where convert(varchar(10),cbstartdate,120) = '2010-05-17' and convert(varchar(10),cbenddate,120) = '2010-05-18'