sql语句查询
我需要查询2013/8/1到2013/8/10之间的每天16：00：00到第二天的05：00：00的数据行数，请问怎么写？
时间格式我知道怎么处理的，同一天的例如16：00：00到23：00：00的数据between一下就可以我也知道，就是跨天的时间段我就不会了，各位大牛帮帮忙呗 SQL
[解决办法]
select *
from tb
where convert(varchar(10),日期字段,120) between '2013-08-01' and '2013-08-10'
and convert(varchar(8),日期字段,108) not between '05:00:00' and '16:00:00'
DROP TABLE #temp
CREATE TABLE #temp([date] DATETIME, field1 INT)
INSERT #temp
SELECT '2013/8/1 16:00:00', 1 UNION ALL
SELECT '2013/8/1 20:00:00', 2 UNION ALL
SELECT '2013/8/2 4:00:00', 3 UNION ALL
SELECT '2013/8/2 5:00:00', 4 UNION ALL
SELECT '2013/8/3 16:00:00', 5 UNION ALL
SELECT '2013/8/10 16:00:00', 6
SELECT * FROM #temp
--sql:
SELECT a.[date], cnt = COUNT(1)
FROM
(
SELECT DISTINCT
[date] = CONVERT(CHAR(10), [date], 120),
begindate = CONVERT(CHAR(10), [date], 120) + ' 16:00:00',
enddate = DATEADD(DAY, 1, CONVERT(CHAR(10), [date], 120)) + ' 05:00:00'
FROM #temp
WHERE [date] BETWEEN '2013/8/1' AND '2013/8/10 23:59:59.997'
) a
INNER JOIN #temp b
ON b.[date] BETWEEN a.begindate AND a.enddate
GROUP BY a.[date]
/*
datecnt
2013-08-014
2013-08-031
2013-08-101
*/
DECLARE @d1 DATETIME,@d2 DATETIME,@d3 DATETIME,@d4 DATETIME,@i1 int,@i2 int,@sql VARCHAR(MAX)
SELECT @d1='2013/8/1',@d2='2013/8/10',@d3=DATEADD(hh,14,@d1),@d4=DATEADD(hh,15,@d3),@i1=1,@i2=DATEDIFF(dd,@d1,@d2)+1,@sql=''
WHILE @i1<=@i2
BEGIN
SET @sql=@sql+CASE WHEN @sql='' THEN '' ELSE ' UNION all ' END+'SELECT * FROM 表 WHERE 日期 BETWEEN'''+ convert(char(19),@d3,20)+''' AND '''+convert(char(19),@d4,20)+''''
SELECT @d3=DATEADD(dd,1,@d3),@d4=DATEADD(hh,15,@d3),@i1=@i1+1
END
exce(@sql)
DECLARE @d1 DATETIME,@d2 DATETIME,@d3 DATETIME,@d4 DATETIME,@i int,@sql VARCHAR(MAX)
SELECT @d1='2013/8/1',@d2='2013/8/10',@d3=DATEADD(hh,14,@d1),@d4=DATEADD(hh,15,@d3),@i=DATEDIFF(dd,@d1,@d2)+1,@sql=''
;
WITH a1 (csql,n) AS
(
SELECT CAST('SELECT * FROM 表 WHERE 日期 BETWEEN '''+ convert(char(19),@d3,20)+''' AND '''+convert(char(19),@d4,20)+'''' AS VARCHAR(MAX)),1
UNION all
SELECT CAST('SELECT * FROM 表 WHERE 日期 BETWEEN '''+ convert(char(19),DATEADD(dd,n,@d3),20)+''' AND '''+convert(char(19),DATEADD(dd,n,@d4),20)+'''' AS VARCHAR(MAX)),n+1
FROM a1
WHERE n<@i
)
SELECT @sql=@sql+CASE WHEN @sql='' THEN '' ELSE ' UNION all ' END+csql
FROM a1
EXEC(@sql)
