关于时间段提取数据的有关问题-MSSQL教程-爱易网页

关于时间段提取数据的有关问题

日期：2014-05-18　浏览次数：20557 次

关于时间段提取数据的问题?
我的表:

id time
1 12:32:33
2 13:42:11
3 15:02:12

现在系统某个表记录着每天每隔半个小时的打卡记录,例如 13:30 ,14:00,14:30,我要提取出所有每半个小时产生的打卡记录,之间我允许有正负五分钟的误差,例如

select * from table where time>='13:25:00' and time<='13:35:00'

但是一天有24小时这么多,那是不是我就一定要这么写,有没有简单点的写法?

select * from table where time>='13:25:00' and time<='13:35:00'
union
select * from table where time>='13:55:00' and time<='14:05:00'
......................
......................
......................

------解决方案--------------------

SQL code


select convert(datetime,convert(varchar(11),getdate(),120)) date,
    dateadd(mi,-5,convert(datetime,convert(varchar(11),getdate(),120)
        +right(100+number/2,2)+':'+right(100+number%2*30,2))) as st,
    dateadd(mi,5,convert(datetime,convert(varchar(11),getdate(),120)
        +right(100+number/2,2)+':'+right(100+number%2*30,2))) as et
from master..spt_values
where [type] = 'p' and number between 0 and 47

/******************

date                    st                      et
----------------------- ----------------------- -----------------------
2011-09-01 00:00:00.000 2011-08-31 23:55:00.000 2011-09-01 00:05:00.000
2011-09-01 00:00:00.000 2011-09-01 00:25:00.000 2011-09-01 00:35:00.000
2011-09-01 00:00:00.000 2011-09-01 00:55:00.000 2011-09-01 01:05:00.000
2011-09-01 00:00:00.000 2011-09-01 01:25:00.000 2011-09-01 01:35:00.000
2011-09-01 00:00:00.000 2011-09-01 01:55:00.000 2011-09-01 02:05:00.000
2011-09-01 00:00:00.000 2011-09-01 02:25:00.000 2011-09-01 02:35:00.000
2011-09-01 00:00:00.000 2011-09-01 02:55:00.000 2011-09-01 03:05:00.000
2011-09-01 00:00:00.000 2011-09-01 03:25:00.000 2011-09-01 03:35:00.000
2011-09-01 00:00:00.000 2011-09-01 03:55:00.000 2011-09-01 04:05:00.000
2011-09-01 00:00:00.000 2011-09-01 04:25:00.000 2011-09-01 04:35:00.000
2011-09-01 00:00:00.000 2011-09-01 04:55:00.000 2011-09-01 05:05:00.000
2011-09-01 00:00:00.000 2011-09-01 05:25:00.000 2011-09-01 05:35:00.000
2011-09-01 00:00:00.000 2011-09-01 05:55:00.000 2011-09-01 06:05:00.000
2011-09-01 00:00:00.000 2011-09-01 06:25:00.000 2011-09-01 06:35:00.000
2011-09-01 00:00:00.000 2011-09-01 06:55:00.000 2011-09-01 07:05:00.000
2011-09-01 00:00:00.000 2011-09-01 07:25:00.000 2011-09-01 07:35:00.000
2011-09-01 00:00:00.000 2011-09-01 07:55:00.000 2011-09-01 08:05:00.000
2011-09-01 00:00:00.000 2011-09-01 08:25:00.000 2011-09-01 08:35:00.000
2011-09-01 00:00:00.000 2011-09-01 08:55:00.000 2011-09-01 09:05:00.000
2011-09-01 00:00:00.000 2011-09-01 09:25:00.000 2011-09-01 09:35:00.000
2011-09-01 00:00:00.000 2011-09-01 09:55:00.000 2011-09-01 10:05:00.000
2011-09-01 00:00:00.000 2011-09-01 10:25:00.000 2011-09-01 10:35:00.000
2011-09-01 00:00:00.000 2011-09-01 10:55:00.000 2011-09-01 11:05:00.000
2011-09-01 00:00:00.000 2011-09-01 11:25:00.000 2011-09-01 11:35:00.000
2011-09-01 00:00:00.000 2011-09-01 11:55:00.000 2011-09-01 12:05:00.000
2011-09-01 00:00:00.000 2011-09-01 12:25:00.000 2011-09-01 12:35:00.000
2011-09-01 00:00:00.000 2011-09-01 12:55:00.000 2011-09-01 13:05:00.000
2011-09-01 00:00:00.000 2011-09-01 13:25:00.000 2011-09-01 13:35:00.000
2011-09-01 00:00:00.000 2011-09-01 13:55:00.000 2011-09-01 14:05:00.000
2011-09-01 00:00:00.000 2011-09-01 14:25:00.000 2011-09-01 14:35:00.000
2011-09-01 00:00:00.000 2011-09-01 14:55:00.000 2011-09-01 15:05:00.000
2011-09-01 00:00:00.000 2011-09-01 15:25:00.000 2011-09-01 15:35:00.000
2011-09-01 00:00:00.000 2011-09-01 15:55:00.000 2011-09-01 16:05:00.000
2011-09-01 00:00:00.000 2011-09-01 16:25:00.000 2011-09-01 16:35:00.000
2011-09-01 00:00:00.000 2011-09-01 16:55:00.000 2011-09-01 17:05:00.000
2011-09-01 00:00:00.000 2011-09-01 17:25:00.000 2011-09-01 17:35:00.000
2011-09-01 00:00:00.000 2011-09-01 17:55:00.000 2011-09-01 18:05:00.000
2011-09-01 00:00:00.000 2011-09-01 18:25:00.000 2011-09-01 18:35:00.000
2011-09-01

免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。

关于时间段提取数据的有关问题

相关资料更多>

推荐阅读更多>