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 |
|
preeti
Starting Member
2 Posts |
 Posted - 2011-09-23 : 07:20:11
|
| i have to check whether a time range say 09:00 AM and 10:00 AM falls betwwen the time ranges saved in db(@from and @to).I i use between operator i won't be able to save the above range if a time say @from =10:00 AM and @to=11:00 AM exists as the time 10:00 AM is colliding with the time range i am trying to save.Please help if someone knows a workaround |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 07:29:15
|
| what are datatypes of time fields in db and also from and @to?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
preeti
Starting Member
2 Posts |
Posted - 2011-09-23 : 08:17:24
|
| varchar but i am converting them like CONVERT(time, @From_Time, 109) and then comparing |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-09-23 : 10:31:58
|
| you want only those records that lies fully in the range or you want even ones with partial overlap as well?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
rcwood0215
Starting Member
1 Post |
Posted - 2011-09-23 : 11:38:53
|
| I am having a similar issue. Not sure if my sinus medicine is making me silly or if I really do not know how to do this query. Here is the issue:I have a scheduling system MS Access 2010 front end with SQL Server back end. I am trying to verify that a specific activity does not occur between times (Say 1:30:00 PM and 2:00:00 PM). The SQL Server field has a datatype of time(0). The problem I am having using a BETWEEN statement in SQL Server is that it brings back records if they start or end at one of those times. I just want to know if an activity I am trying to add falls between those times. If it starts the same time one ends, that is fine. I also do not want the end time to fall after a start time of another activity. It can end at the same time an activity begins. If that makes sense, tell me what I am doing wrong. This is what I have:SELECT IDOLAppt, ApptDate, ApptTime, ApptEndTime, IDAssetFROM APPPOINTMENTWHERE IDAsset = 2 AND ApptDate = '2011-10-13' AND ApptTime Between '1:00:00 PM' AND '1:30:00 PM'OR IDAsset = 2 AND ApptDate = '2011-10-13' AND ApptEndTime Between '1:00:00 PM' AND '1:30:00 PM'So is there anyway to get this statement to work like MS Access where if the start time of one activity is 1:00:00 PM and the end time of another is 1:00:00 PM, sql does not see a conflict?Thanks in advance.Chris WoodardProgrammer/Analyst/DBAUC, San Diego PACE Program |
|
|
|
|
|
|
time range