Posted by Jim Armstrong on 05/03/06 18:28

Hi all -

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

• Next in forum: SQL Counting number of non-distinct rows?
• Prev in forum: Re: An odd quandry.
• Thread view: Problem with using BETWEEN for date range...

[Reply to this message]