| Author |
Topic |
|
sqldv
Starting Member
2 Posts |
 Posted - 2006-07-27 : 10:10:47
|
| Helo. I'm creating a query that which going to return a record count from a Resource Reserve table, where this table have start date and end date. It will keep track the start date for user to reserve the resources, however the end date is when user return the resource. So, problem here, i would like to have a query to look for a record count where the user start reserve resource date or end reserve date must not fall in between the time frame in the db (startdate and endate).As, I have try with several query but fail to retrieve correct record count.I used select count(*) from ResourceManager where (dtStartdate not between Convert(datetime, "'& RSTartDate &'",131) and Convert(datetime, "'& REndDate &'",131) and (dtEnddate not between Convert(datetime, "'& RSTartDate &'",131) and Convert(datetime, "'& REndDate &'",131))All return me record count 0Hopefully anybody can help me in this problem. Thanks. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-07-27 : 10:17:30
|
| Probably time part in the db date columns is creating problem:try this...select count(*) from ResourceManager where (dateadd(d,0,datediff(d,0,dtStartdate)) not between Convert(datetime, "'& RSTartDate &'",131) and Convert(datetime, "'& REndDate &'",131) and (dateadd(d,0,datediff(d,0,dtEnddate)) not between Convert(datetime, "'& RSTartDate &'",131) and Convert(datetime, "'& REndDate &'",131)))Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-07-27 : 10:25:38
|
| Where dtStartdate not between (DateAdd(day,DateDiff(day,0,RSTartDate),0) and DateAdd(day,DateDiff(day,0,REndDate),0))and dtEnddate not between (DateAdd(day,DateDiff(day,0,RSTartDate),0) and DateAdd(day,DateDiff(day,0,REndDate),0))MadhivananFailing to plan is Planning to fail |
|
|
|
|
|
|
DateTime Comparison Problem