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.
Author |
Topic |
akpaga
Constraint Violating Yak Guru
331 Posts |
Posted - 2011-07-15 : 12:21:22
|
HiI have field call timeSlot which has data stored in varchar in the follwing way1-45-72-32-4I 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 YourTableWHERE LEFT(timeSlot,CHARINDEX('-',timeSlot)-1) >= 1 AND STUFF(timeSlot,1,CHARINDEX('-',timeSlot),'') <= 4 |
|
|
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 YourTableWHERE LEFT(timeSlot,CHARINDEX('-',timeSlot)-1) BETWEEN 1 AND 4 OR STUFF(timeSlot,1,CHARINDEX('-',timeSlot),'') BETWEEN 1 AND 4 |
|
|
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 checksexample 10-12if current time is 11:40 since 12-11:40 < 1 hr the show 1else 0...hope this example is clear... |
|
|
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) |
|
|
|
|
|
|
|