Well, your very big goof was putting ticket_date and ticket_time into separate fields.
As you have it now, you have an IMPOSSIBLE TO MEET set of conditions.
Please understand that when you have a WHERE clause where all the conjunctions are AND, then your parentheses are not doing anything, at all.
So your query
Code:
select * from salestkt
where (ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM')
and (ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM')
Is 100% the same as
Code:
select * from salestkt
where ticket_date >= '2008-10-20' and ticket_time >= '06:00:00 AM'
and ticket_date <= '2008-10-22' and ticket_time <= '06:00:00 AM'
which is 100% the same as
Code:
select * from salestkt
where ticket_date >= '2008-10-20' and ticket_date <= '2008-10-22'
and ticket_time >= '06:00:00 AM' and ticket_time <= '06:00:00 AM'
So now just look at those last two conditions:
Code:
and ticket_time >= '06:00:00 AM' and ticket_time <= '06:00:00 AM'
And of course there is
NO POSSIBLE VALUE for ticket_time that can satisfy BOTH of those conditions, so of course you get zero results.
KABLOOEY.
You really really really should combine ticket_date and ticket_time into a single DB field.
But I *THINK* you can do the following with SQL SERVER:
Code:
select * from salestkt
WHERE (ticket_date + ticket_time ) BETWEEN '2008-10-20 06:00:00 AM' AND '2008-10-22 06:00:00 AM'
That is, I *think* that in SQL Server you can simply add a date and time together to produce a datetime value.
If not, then we can do it with DATEADD and DATEDIFF function calls. But try that first.