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] /* date cnt 2013-08-01 4 2013-08-03 1 2013-08-10 1 */
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 a1EXEC(@sql)
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'
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]
/*
date cnt
2013-08-01 4
2013-08-03 1
2013-08-10 1
*/
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 a1EXEC(@sql)