Problem with using BETWEEN for date range

SQL Apprentice Question
This problem has been driving me crazy, and I'm hoping the answer is
something stupid I am neglecting to see....


The procedure posted below is part of an Access/SQL database I have
developed. Basically, the user would input a beginning and ending date,
and the query goes and pulls records that meet the following criteria:


1. TradeDate is between beginning date and ending date
2. TradeTime is between beginning date and ending date
3. Trade's Match ID is equal to match IDs returned by the second select
statement - this is part of a ticket processing system, and tickets are
grouped using their match id. So, if one ticket has been updated and
now meets criteria #1 or #2 above, this is supposed to also return any
of the other tickets with the same match ID - so if one ticket in a
group changes, our acct. dept can look at the whole group on their
reports.


Anyway, the query below seems to work, but I am not happy with it. The
problem was I was using the BETWEEN function, and not converting all
the dates to varchar. This worked fine, unless the beginning date and
ending date were the same. For example, if I had a ticket with a
tradedate of 5/3/06 and I ran a beginning date of 5/3/06 and an ending
date of 5/3/06, the ticket should be returned. However, with the
BETWEEN statement, it would return no rows.


I changed the BETWEEN statements to statements like:


tradedate >= beginning date and tradedate <= ending date


but this also returned no rows.


It was only upon converting all the dates to varchar and using the <=
and >= operators that I started getting the results I need.


Can someone tell me why the heck BETWEEN wouldn't work? Tradedate and
Tradetime are both datetime values, and I was bringing in the beginning
and ending date variables in datetime form...is there a problem using
BETWEEN when the first and second variables used are the same?


Any light you can shed on this would be great, because having all these
convert statements and such makes me nervous...I'd rather get between
to work, but I have not been able to in my testing...


Thanks! -Jim


CREATE PROCEDURE dbo.spAcctExport(@begindate datetime,
@enddate datetime)
AS SELECT TOP 100 PERCENT dbo.tblTradeAccount.AccountingCode AS
TradeAccount, dbo.tblOrders.TicketNum, dbo.tblOrders.TradeDate,
dbo.tblOrders.SettleDate, NULL AS
ProductionMonth, dbo.tblOrders.RepID, dbo.tblOrders.AcctNum,
dbo.tblAccounts.Shortname, dbo.tblOrders.Quantity,
dbo.TBLCUSIP.Factor, dbo.tblOrders.BuySell,
dbo.tblOrders.CUSIP, dbo.TBLCUSIP.Issuer, dbo.TBLCUSIP.PoolNum,
dbo.TBLCUSIP.Coupon,
dbo.tblOrders.FixAdj, dbo.tblOrders.Price,
dbo.tblOrders.RepSC, '=(H:H*I:I*Q:Q)/100' AS Markup, 'PTMSA' AS
ProdType, dbo.tblOrders.DeskSC,
dbo.tblOrders.RepCarry, '=(H:H*I:I*T:T)/100' AS
DeskMarkup, dbo.tblOrders.MatchID,


'=IF(K:K="B",((I:I*H:H*P:P)/100)*-1,(I:I*H:H*P:P)/100)' AS
TotalPrincipal, dbo.tblOrders.CancelCorrect,
dbo.tblOrders.OriginalTrade,
dbo.tblOrders.TradeTime, dbo.tblOrders.Rep2ID,
dbo.tblOrders.Rep2SC
FROM dbo.tblOrders INNER JOIN
dbo.TBLCUSIP ON dbo.tblOrders.CUSIP =
dbo.TBLCUSIP.CUSIP INNER JOIN
dbo.tblAccounts ON dbo.tblOrders.AcctNum =
dbo.tblAccounts.AcctNum INNER JOIN
dbo.tblTradeAccount ON dbo.tblOrders.TradeAccount
= dbo.tblTradeAccount.TradeAccount
WHERE ((


(dbo.tblOrders.MatchID IS NOT NULL) AND (dbo.tblOrders.MatchID IN
(SELECT MatchID FROM dbo.tblOrders WHERE (


CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
CONVERT(VARCHAR(10), @begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
CONVERT(VARCHAR(10), @enddate,101))


OR (


CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=
CONVERT(VARCHAR(10), @begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
CONVERT(VARCHAR(10), @enddate,101)))))


OR ((


CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
CONVERT(VARCHAR(10), @begindate,101) and
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
CONVERT(VARCHAR(10), @enddate,101))


OR (


CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=
CONVERT(VARCHAR(10), @begindate,101) AND
CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
CONVERT(VARCHAR(10), @enddate,101))))


ORDER BY dbo.tblOrders.CancelCorrect,
dbo.tblTradeAccount.AccountingCode, dbo.tblOrders.MatchID,
dbo.tblOrders.BuySell, dbo.tblOrders.TicketNum
GO


Celko Answers
In SQL Server, there is not DATE data type or TIME data type -- but
there is a single DATETIME. Quit doing all that converting and work
with temporal expressions instead of COBOL-style strings. Think in
abstract terms, not how it is displayed on a piece of paper or a
screen.

WHERE trade_time BETWEEN start_time AND end_time


will work fine, if you bother to add constraints to start_time (i.e
00:00:00) and end_time (23:59:59.9999) columns. Another trick is to
allow end_time to be NULL when an event is still in progress and then
use COALESCE (end_time, CURRENT_TIMESTAMP)


If you look up Rick Snodgrass at University of Arizona, you can
download his out-of-print book on temporal SQL queries.