我的表:
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 etfrom master..spt_valueswhere [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.0002011-09-01 00:00:00.000 2011-09-01 00:25:00.000 2011-09-01 00:35:00.0002011-09-01 00:00:00.000 2011-09-01 00:55:00.000 2011-09-01 01:05:00.0002011-09-01 00:00:00.000 2011-09-01 01:25:00.000 2011-09-01 01:35:00.0002011-09-01 00:00:00.000 2011-09-01 01:55:00.000 2011-09-01 02:05:00.0002011-09-01 00:00:00.000 2011-09-01 02:25:00.000 2011-09-01 02:35:00.0002011-09-01 00:00:00.000 2011-09-01 02:55:00.000 2011-09-01 03:05:00.0002011-09-01 00:00:00.000 2011-09-01 03:25:00.000 2011-09-01 03:35:00.0002011-09-01 00:00:00.000 2011-09-01 03:55:00.000 2011-09-01 04:05:00.0002011-09-01 00:00:00.000 2011-09-01 04:25:00.000 2011-09-01 04:35:00.0002011-09-01 00:00:00.000 2011-09-01 04:55:00.000 2011-09-01 05:05:00.0002011-09-01 00:00:00.000 2011-09-01 05:25:00.000 2011-09-01 05:35:00.0002011-09-01 00:00:00.000 2011-09-01 05:55:00.000 2011-09-01 06:05:00.0002011-09-01 00:00:00.000 2011-09-01 06:25:00.000 2011-09-01 06:35:00.0002011-09-01 00:00:00.000 2011-09-01 06:55:00.000 2011-09-01 07:05:00.0002011-09-01 00:00:00.000 2011-09-01 07:25:00.000 2011-09-01 07:35:00.0002011-09-01 00:00:00.000 2011-09-01 07:55:00.000 2011-09-01 08:05:00.0002011-09-01 00:00:00.000 2011-09-01 08:25:00.000 2011-09-01 08:35:00.0002011-09-01 00:00:00.000 2011-09-01 08:55:00.000 2011-09-01 09:05:00.0002011-09-01 00:00:00.000 2011-09-01 09:25:00.000 2011-09-01 09:35:00.0002011-09-01 00:00:00.000 2011-09-01 09:55:00.000 2011-09-01 10:05:00.0002011-09-01 00:00:00.000 2011-09-01 10:25:00.000 2011-09-01 10:35:00.0002011-09-01 00:00:00.000 2011-09-01 10:55:00.000 2011-09-01 11:05:00.0002011-09-01 00:00:00.000 2011-09-01 11:25:00.000 2011-09-01 11:35:00.0002011-09-01 00:00:00.000 2011-09-01 11:55:00.000 2011-09-01 12:05:00.0002011-09-01 00:00:00.000 2011-09-01 12:25:00.000 2011-09-01 12:35:00.0002011-09-01 00:00:00.000 2011-09-01 12:55:00.000 2011-09-01 13:05:00.0002011-09-01 00:00:00.000 2011-09-01 13:25:00.000 2011-09-01 13:35:00.0002011-09-01 00:00:00.000 2011-09-01 13:55:00.000 2011-09-01 14:05:00.0002011-09-01 00:00:00.000 2011-09-01 14:25:00.000 2011-09-01 14:35:00.0002011-09-01 00:00:00.000 2011-09-01 14:55:00.000 2011-09-01 15:05:00.0002011-09-01 00:00:00.000 2011-09-01 15:25:00.000 2011-09-01 15:35:00.0002011-09-01 00:00:00.000 2011-09-01 15:55:00.000 2011-09-01 16:05:00.0002011-09-01 00:00:00.000 2011-09-01 16:25:00.000 2011-09-01 16:35:00.0002011-09-01 00:00:00.000 2011-09-01 16:55:00.000 2011-09-01 17:05:00.0002011-09-01 00:00:00.000 2011-09-01 17:25:00.000 2011-09-01 17:35:00.0002011-09-01 00:00:00.000 2011-09-01 17:55:00.000 2011-09-01 18:05:00.0002011-09-01 00:00:00.000 2011-09-01 18:25:00.000 2011-09-01 18:35:00.0002011-09-01 00:00:00.000 2011-09-01 18:55:00.000 2011-09-01 19:05:00.0002011-09-01 00:00:00.000 2011-09-01 19:25:00.000 2011-09-01 19:35:00.0002011-09-01 00:00:00.000 2011-09-01 19:55:00.000 2011-09-01 20:05:00.0002011-09-01 00:00:00.000 2011-09-01 20:25:00.000 2011-09-01 20:35:00.0002011-09-01 00:00:00.000 2011-09-01 20:55:00.000 2011-09-01 21:05:00.0002011-09-01 00:00:00.000 2011-09-01 21:25:00.000 2011-09-01 21:35:00.0002011-09-01 00:00:00.000 2011-09-01 21:55:00.000 2011-09-01 22:05:00.0002011-09-01 00:00:00.000 2011-09-01 22:25:00.000 2011-09-01 22:35:00.0002011-09-01 00:00:00.000 2011-09-01 22:55:00.000 2011-09-01 23:05:00.0002011-09-01 00:00:00.000 2011-09-01 23:25:00.000 2011-09-01 23:35:00.000(48 行受影响)