Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Check time slot for varchar field

Author  Topic 

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-07-15 : 12:21:22
Hi

I have field call timeSlot which has data stored in varchar in the follwing way

1-4
5-7
2-3
2-4

I want to select all records which fall under the 1-4 time zone...

ie from above example 1-4,2-3,2-4 records should show up..
Can someone help me on this

Thanks...

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 13:07:16
Something like this should work, but not tested. It depends on the data always being two numbers separated by a hyphen. If not, the code would fail.
SELECT
*
FROM
YourTable
WHERE
LEFT(timeSlot,CHARINDEX('-',timeSlot)-1) >= 1
AND STUFF(timeSlot,1,CHARINDEX('-',timeSlot),'') <= 4
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 13:21:35
I have the wrong logic in my previous posting.
SELECT
*
FROM
YourTable
WHERE
LEFT(timeSlot,CHARINDEX('-',timeSlot)-1) BETWEEN 1 AND 4
OR STUFF(timeSlot,1,CHARINDEX('-',timeSlot),'') BETWEEN 1 AND 4
Go to Top of Page

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2011-07-15 : 15:58:13
thanks sunita..
I have one more issue..

I want to check the end time with current time and do the following checks

example 10-12

if current time is 11:40

since 12-11:40 < 1 hr the show 1
else 0...hope this example is clear...
Go to Top of Page

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-15 : 20:22:52
You can subtract the hour part of the current time to get this, for example:

STUFF(timeSlot,1,CHARINDEX('-',timeSlot),'') - datepart(hour,CURRENT_TIMESTAMP)
Go to Top of Page
   

- Advertisement -