使用SQL语句查询时间段

使用SQL语句查询时间段在项目中，如果需要查询时间段，该SQL语句应该如何写呢？下面就将教您使用SQL语句查询时间段的方法，希望对您有所启迪。假设在SQL SERVER2000数据库中有一个表，表名为book，设置两个日期框，分别起名为起始时间和终止时间，其中在book中有一个DATETIME类型的字段，字段名假设为查询时间。由于在SQL中只能用文本，所以先设一个时间转换为文本的子程序。子程序名：时间格式 返回类型：文本型参数：转换时间 类型：日期时间型变量：时间 类型：文本型变量：年 类型：文本型变量：月 类型：文本型变量：日 类型：文本型年=到文本(取年份(转换时间)月=到文本(取月份(转换时间)日=到文本(取日(转换时间)时间=年+-+月+-+日返回(时间)设置一个全局变量，变量名为记录集，类型为整数型，下面开始查询时间。记录集=外部数据库。查询(select * from book wher 查询时间 between +时间格式(起始时间。今天)+ and +时间格式(终止时间。今天)+)说明：在这里要特别注意between要空一格然后加上一个单引号，and之前和之后都要空一格然后加上一个单引号。显示记录()用sql语句按指定时间段分组统计- 生成测试数据: #tbCREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT 03174190188,2009-11-01 07:17:39.217 UNION ALLSELECT 015224486575,2009-11-01 08:01:17.153 UNION ALLSELECT 013593006926,2009-11-12 08:04:46.560 UNION ALLSELECT 013599584239,2009-11-22 08:53:27.763 UNION ALLSELECT 013911693526,2009-11-23 08:53:51.683 UNION ALLSELECT 013846472440,2009-11-23 08:54:57.233 UNION ALLSELECT 013990353697,2009-11-24 08:55:25.077 UNION ALLSELECT 013990353697,2009-11-25 08:56:01.327 UNION ALLSELECT 013945594843,2009-11-26 08:57:02.233 UNION ALLSELECT 013990353697,2009-11-27 08:57:29.700 UNION ALLSELECT 013916597421,2009-11-28 08:59:49.390 UNION ALLSELECT 03916995857,2009-11-29 09:11:05.607 UNION ALLSELECT 015097712001,2009-11-30 09:13:50.293-SQL查询如下:DECLARE minDate datetime,maxDate datetime;SELECT minDate = 2009-11-1,maxDate = 2009-12-01;DECLARE sql varchar(8000);SET sql = ;SELECT sql=sql+,SUM(CASE WHEN DATEDIFF(day,B.时间, +CONVERT(varchar(10),DATEADD(day,number,minDate),120) +)=0 THEN 1 ELSE 0 END) AS +CONVERT(varchar(10),DATEADD(day,number,minDate),120)+FROM master.dbo.spt_valuesWHERE type = P AND DATEADD(day,number,minDate)=maxDate;print sqlDECLARE cmd nvarchar(4000);SET cmd = NSELECT ISNULL(A.时段,合计) AS 时段+sql+, COUNT(列名1) AS 合计FROM( SELECT 时段=RIGHT(100+number,2)+:00+RIGHT(100+number+1,2)+:00, MinDate = RIGHT(100+number,2)+:00:00, MaxDate = RIGHT(100+number+1,2)+:00:00 FROM master.dbo.spt_values WHERE type = P AND number = A.MinDate AND CONVERT(varchar(8),B.时间,108) 生成测试数据: #tbCREATE TABLE #tb(列名1 varchar(12),时间 datetime)INSERT INTO #tbSELECT 03174190188,2009-11-01 07:17:39.217 UNION ALLSELECT 015224486575,2009-11-01 08:01:17.153 UNION ALLSELECT 013593006926,2009-11-12 08:04:46.560 UNION ALLSELECT 013599584239,2009-11-22 08:53:27.763 UNION ALLSELECT 013911693526,2009-11-23 08:53:51.683 UNION ALLSELECT 013846472440,2009-11-23 08:54:57.233 UNION ALLSELECT 013990353697,2009-11-24 08:55:25.077 UNION ALLSELECT 013990353697,2009-11-25 08:56:01.327 UNION ALLSELECT 013945594843,2009-11-26 08:57:02.233 UNION ALLSELECT 013990353697,2009-11-27 08:57:29.700 UNION ALLSELECT 013916597421,2009-11-28 08:59:49.390 UNION ALLSELECT 03916995857,2009-11-29 09:11:05.607 UNION ALLSELECT 015097712001,2009-11-30 09:13:50.293-SQL查询如下:DECLARE minDate datetime,maxDate datetime;SELECT minDate = 2009-11-1,maxDate = 2009-12-01;select convert(char(10),dateadd(d,number,minDate),120),sum(case when convert(char(8),时间,108) between 00:00 and 01:00 then 1 else 0 end) as 00:0001:00,sum(case when convert(char(8),时间,108) between 01:00 and 02:00 then 1 else 0 end) as 01:0002:00,sum(case when convert(char(8),时间,108) between 02:00 and 03:00 then 1 else 0 end) as 02:0003:00,sum(case when convert(char(8),时间,108) between 03:00 and 04:00 then 1 else 0 end) as 03:0004:00,sum(case when convert(char(8),时间,108) between 04:00 and 05:00 then 1 else 0 end) as 04:0005:00,sum(case when convert(char(8),时间,108) between 05:00 and 06:00 then 1 else 0 end) as 05:0006:00,sum(case when convert(char(8),时间,108) between 06:00 and 07:00 then 1 else 0 end) as 06:0007:00,sum(case when convert(char(8),时间,108) between 07:00 and 08:00 then 1 else 0 end) as 07:0008:00,sum(case when convert(char(8),时间,108) between 08:00 and 09:00 then 1 else 0 end) as 08:0009:00,count(a.列名1) as sumfrom #tb a righ