如何在SQL Server 2008中过滤时隙 [英] How to filter timeslots in SQL server 2008
问题描述
我必须找到列的确切ID,其中当前时间位于时隙列的时隙之间。
我的表结构是:
id时间段
1 12:00 AM至02:00 AM
2 02:00 AM至04:00 AM
3 04:00 AM至06:00 AM
4 06:00 AM至08:00 AM
5 08:00 AM至10:00 AM
6 10:00 AM至12:00 PM
7 12:00 PM To 02:00 PM
8 02:00 PM至04:00 PM
9 04:00 PM至06:00 PM
10 06:00 PM至08:00 PM
11 08:00 PM至10:00 PM
12 10:00 PM至12:00 AM
例如: '2:58 PM'介于
02:00 PM到04:00 PM
所以id为8
i想要那个id
我尝试了什么:
SELECT [id],[CollectionTimeSlot]
FROM tbl_CollectionTimeSlot WHERE ' 2:40 PM' BETWEEN convert ( nvarchar ( 20 ),SUBSTRING([CollectionTimeSlot], 0 ,CHARINDEX(' To',[CollectionTimeSlot] )), 108 )
AND convert ( nvarchar ( 20 ),SUBSTRING([CollectionTimeSlot],CHARINDEX(' To',[CollectionTimeSlot])+ 3,LEN([CollectionTimeSlot])))
还试过另一个:
SELECT [id],[CollectionTimeSlot]
FROM tbl_CollectionTimeSlot WHERE ' 04:05 AM'> = convert ( nvarchar ( 20 ),SUBSTRING([CollectionTimeSlot], 0 ,CHARINDEX(' To',[CollectionTimeSlot])), 110 )
AND ' 04:05 AM'< = convert ( nvarchar (< span class =code-digit> 20 ),SUBSTRING([CollectionTimeSlot],CHARINDEX(' To ',[CollectionTimeSlot])+ 3,LEN([CollectionTimeSlot])))
给我一个结果:
id CollectionTimeSlot
1 12:00 AM至02:00 AM
7 12:00 PM至02:00 PM
我需要精确ID
只要时间数据的格式正确,此查询将与您当前的表格结构一起使用 - 即在值小于10时使用前面的零。
DECLARE @ testTime TIME = CAST(' 02:58 PM' AS TIME )
SELECT [id],[CollectionTimeSlot],
CAST(SUBSTRING([CollectionTimeSlot], 1 , 8 ) AS TIME ),
CAST(SUBSTRING([CollectionTimeSlot], 13 , 8 ) AS TIME )
FROM tbl_CollectionTimeSlot
WHERE @ testTime BETWEEN CAST(SUBSTRING([CollectionTimeSlot], 1 , 8 ) AS TIME ) AND CAST(SUBSTRING([CollectionTimeSlot], 13 , 8 ) AS TIME )WHERE语句中的BETWEEN与
<完全相同span class =code-keyword> WHERE @ testTime > = CAST(SUBSTRING([CollectionTimeSlot], 1 , 8 ) AS TIME ) AND@ testTime < = CAST(SUBSTRING([CollectionTimeSlot], 13 , 8 ) AS TIME )
但是,这是一个糟糕的表模式!
始终为表列使用正确的数据类型。 Varchar(或char,或nvarchar或nchar)是不 TIME数据的适当数据类型。
这样会更好:
创建 表 CollectionTimeSlot
(
id int identity ( 1 , 1 ),
startTime TIME ,
endTime TIME
)
insert 进入 CollectionTimeSlot 值
(' 12:00 AM' ,' 02:00 AM'),
(' 02:00 AM',' 04:00 AM'),
(' 04:00 AM',' 06:00 AM'),
(' 06:00 AM',' 08:00 AM'),
(' 08:00 AM ',' 10:00 AM'),
(' 10:00 AM',' 12:00 PM'),
(' 12: 00 PM',' 02:00 PM'),
(< span class =code-string>' 02:00 PM',' < span class =code-string> 04:00 PM'),
(' 04:00 PM',' 06:00 PM'),
(' 06:00 PM',' 08:00 PM'),
(' 08:00 PM',' 10:00 PM'),
(' 10:00 PM',' 12:00 AM')然后查询数据变得微不足道
DECLARE @ testTime2 TIME = CAST(' 02:58 PM' AS TIME )
SELECT * FROM CollectionTimeSlot
WHERE @ testTime2 BETWEEN startTime AND endTime
创建表#CollectionTimeSlot
(
id int identity(1,1),
Timeslot VARCHAR(100),
)
插入#CollectionTimeSlot值
('12:00 AM到02:00 AM'),
(' 02:00 AM至04:00 AM'),
('04:00 AM至06:00 AM'),
('06:00 AM至08:00 AM'),
('08:00 AM至10:00 AM'),
('10:00 AM至12:00 PM'),
('12:00 PM至02:00 PM' ),
('02:00 PM到04:00 PM'),
('04:00 PM到06:00 PM'),
('06:00 PM到08 :00 PM'),
('08:00 PM至10:00 PM'),
('10:00 PM至12:00 AM')
SELECT * FROM #CollectionTimeSlot
DECLARE @ testTime2 TI ME = CAST('02:58 PM'AS TIME)
选择@ testTime2
SELECT * FROM #CollectionTimeSlot
WHERE @ testTime2 BETWEEN CAST(左(时间段) ,8)as TIME)和CAST(Right(Timeslot,8)as TIME)
i have to find exact id of column where my current time is lies between time slots in time slot column.
my table structure is:
id timeslot
1 12:00 AM To 02:00 AM
2 02:00 AM To 04:00 AM
3 04:00 AM To 06:00 AM
4 06:00 AM To 08:00 AM
5 08:00 AM To 10:00 AM
6 10:00 AM To 12:00 PM
7 12:00 PM To 02:00 PM
8 02:00 PM To 04:00 PM
9 04:00 PM To 06:00 PM
10 06:00 PM To 08:00 PM
11 08:00 PM To 10:00 PM
12 10:00 PM To 12:00 AM
for eg: '2:58PM' lies between
02:00 PM To 04:00 PM
so id is "8"
i want that id
What I have tried:
SELECT [id],[CollectionTimeSlot]
FROM tbl_CollectionTimeSlot WHERE '2:40 PM' BETWEEN convert(nvarchar(20), SUBSTRING([CollectionTimeSlot] ,0, CHARINDEX('To', [CollectionTimeSlot]) ),108)
AND convert(nvarchar(20),SUBSTRING([CollectionTimeSlot] , CHARINDEX('To', [CollectionTimeSlot])+3,LEN([CollectionTimeSlot])))
ALSO TRIED ANOTHER ONE :
SELECT [id],[CollectionTimeSlot]
FROM tbl_CollectionTimeSlot WHERE '04:05 AM'>= convert(nvarchar(20), SUBSTRING([CollectionTimeSlot] ,0, CHARINDEX('To', [CollectionTimeSlot]) ),110)
AND '04:05 AM'<= convert(nvarchar(20),SUBSTRING([CollectionTimeSlot] , CHARINDEX('To', [CollectionTimeSlot])+3,LEN([CollectionTimeSlot])))
WHICH GIVE ME A RESULT AS:
id CollectionTimeSlot
1 12:00 AM To 02:00 AM
7 12:00 PM To 02:00 PM
I NEED EXACT ID
This query will work with your current table construction as long as the time data is in the correct long format - i.e. with preceding zeroes on values less than 10.
DECLARE @testTime TIME = CAST('02:58 PM' AS TIME) SELECT [id],[CollectionTimeSlot], CAST(SUBSTRING([CollectionTimeSlot],1,8) AS TIME), CAST(SUBSTRING([CollectionTimeSlot],13,8) AS TIME) FROM tbl_CollectionTimeSlot WHERE @testTime BETWEEN CAST(SUBSTRING([CollectionTimeSlot],1,8) AS TIME) AND CAST(SUBSTRING([CollectionTimeSlot],13,8) AS TIME)The BETWEEN on the WHERE statement is exactly the same as saying
WHERE @testTime >= CAST(SUBSTRING([CollectionTimeSlot],1,8) AS TIME) AND @testTime <= CAST(SUBSTRING([CollectionTimeSlot],13,8) AS TIME)
However, that is an awful table schema!
Always use the correct data type for your table columns. Varchar (or char, or nvarchar or nchar) are not appropriate data types for TIME data.
This would have been better:
create table CollectionTimeSlot ( id int identity(1,1), startTime TIME, endTime TIME ) insert into CollectionTimeSlot values ('12:00 AM','02:00 AM'), ('02:00 AM','04:00 AM'), ('04:00 AM','06:00 AM'), ('06:00 AM','08:00 AM'), ('08:00 AM','10:00 AM'), ('10:00 AM','12:00 PM'), ('12:00 PM','02:00 PM'), ('02:00 PM','04:00 PM'), ('04:00 PM','06:00 PM'), ('06:00 PM','08:00 PM'), ('08:00 PM','10:00 PM'), ('10:00 PM','12:00 AM')Then querying the data becomes trivial
DECLARE @testTime2 TIME = CAST('02:58 PM' AS TIME) SELECT * FROM CollectionTimeSlot WHERE @testTime2 BETWEEN startTime AND endTime
Create table #CollectionTimeSlot ( id int identity(1,1), Timeslot VARCHAR(100), ) insert into #CollectionTimeSlot values ('12:00 AM To 02:00 AM'), ('02:00 AM To 04:00 AM'), ('04:00 AM To 06:00 AM'), ('06:00 AM To 08:00 AM'), ('08:00 AM To 10:00 AM'), ('10:00 AM To 12:00 PM'), ('12:00 PM To 02:00 PM'), ('02:00 PM To 04:00 PM'), ('04:00 PM To 06:00 PM'), ('06:00 PM To 08:00 PM'), ('08:00 PM To 10:00 PM'), ('10:00 PM To 12:00 AM') SELECT * FROM #CollectionTimeSlot DECLARE @testTime2 TIME = CAST('02:58 PM' AS TIME) Select @testTime2 SELECT * FROM #CollectionTimeSlot WHERE @testTime2 BETWEEN CAST(Left(Timeslot,8) as TIME) AND CAST(Right(Timeslot,8) as TIME)
这篇关于如何在SQL Server 2008中过滤时隙的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!