Because there's no such thing in Oracle as a date without a time. It defaults to midnight, I believe, on a standard install. So, while you may not have put a time in - it is in there.
You have two options - format the startdate and finishdate to truncate their time portions - so that you're just limiting on date OR add time portions to your periodend (23:59:59) and periodstart (00:00:01). -d > in the above instance periodend = '11/30/2004' > and periodstart = '11/30/2004' > > so why doesnt > WHERE t.startdate (+) <= p.periodend > AND t.finishdate (+) >= p.periodstart > pick up that task? > But > WHERE t.startdate (+) <= p.periodend + 1 > AND t.finishdate (+) >= p.periodstart > does > and without the joins > WHERE trunc(t.startdate) <= p.periodend > AND trunc(t.finishdate) >= p.periodstart > works. > > Can someone explain?.. > > Thanks! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Special thanks to the CF Community Suite Gold Sponsor - CFHosting.net http://www.cfhosting.net Message: http://www.houseoffusion.com/lists.cfm/link=i:4:184738 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
