How to filter timeslots in SQL server 2008

Question

1.00/5 (1 vote)

See more:

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:

HTML

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:

SQL

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

Posted 21-Mar-17 23:31pm

Member 11466758

Updated 22-Mar-17 7:35am

User 4180254

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2017-03-22T07:35:00

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.

SQL

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

SQL

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:

SQL

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

SQL

DECLARE @testTime2 TIME = CAST('02:58 PM' AS TIME)

SELECT * FROM CollectionTimeSlot 
WHERE @testTime2 BETWEEN startTime AND endTime

KapilMuni · Answer 2 · 2017-03-23T04:08:00

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)

How to filter timeslots in SQL server 2008

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0