使用BETWEEN作为日期范围的问题... [英] Problem with using BETWEEN for date range...
问题描述
大家好 -
这个问题一直让我发疯，我希望答案是好的。
一些愚蠢的东西我忽略了看....
下面发布的程序是我开发的
开发的Access / SQL数据库的一部分。基本上，用户将输入开始和结束日期，
并且查询将获取符合以下条件的记录：
1. TradeDate介于两者之间开始日期和结束日期
2.交易时间在开始日期和结束日期之间
3.交易的匹配ID等于第二个选择返回的匹配ID
语句 - 这是票证处理系统的一部分，票证是使用他们的匹配ID分组的。
。所以，如果一张票已经更新并且
现在符合上面的标准＃1或＃2，那么这也应该返回任何
的其他票与同一场比赛ID - 所以如果
组中的一张票改变了，我们的帐号。部门可以在他们的
报告中查看整个小组。
无论如何，下面的查询似乎有效，但我对它不满意。
问题是我使用BETWEEN函数，而不是将所有日期转换为varchar。除非开始日期和
结束日期相同，否则此工作正常。例如，如果我的票价为5/3/06的
交易，我的开始日期为5/3/06，并且结束
日期为5/3/06，应退回机票。但是，使用
BETWEEN语句，它将不会返回任何行。
我将BETWEEN语句更改为以下语句：
tradedate> =开始日期和交易日期< =结束日期
但这也没有返回任何行。
这是只有在将所有日期转换为varchar并使用< =
和> =运算符后，我才开始获得我需要的结果。
可以有人告诉我为什么BETWEEN不能工作？交易和
交易时间都是日期时间值，我在开始时带来了
和日期时间形式的结束日期变量...使用
时出现问题当使用的第一个和第二个变量相同时，
BETWEEN？
任何你可以使用的灯都很棒，因为拥有所有这些
转换语句等让我感到紧张......我宁愿在
之间工作，但我还没能参加我的测试...
谢谢！ -Jim
创建程序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标记，''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
来自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），d bo.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）和
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） ，@）
ORDER BY dbo.tblOrders.CancelCorrect，
dbo.tblTradeAccount.AccountingCode，dbo.tblOrders.MatchID，
dbo.tblOrders.BuySell，dbo.tblOrders.TicketNum
GO
< blockquote>在SQL Serv中呃，没有DATE数据类型或TIME数据类型 - 但是
只有一个DATETIME。退出所有转换和工作
与时间表达式而不是COBOL样式的字符串。想想
抽象术语，而不是如何在一张纸上或
屏幕上显示。
WHERE trade_time BETWEEN start_time和end_time
如果你懒得为start_time添加约束（即
00:00:00）和end_time（23:59： 59.9999）列。另一个技巧是
允许end_time在事件仍在进行时为NULL然后
使用COALESCE（end_time，CURRENT_TIMESTAMP）
如果你在亚利桑那大学查找Rick Snodgrass，你可以下载他关于时态SQL查询的绝版书。
好的 - 我的帖子提到我最初使用
WHERE交易BETWEEN begindate AND enddate
Begindate和enddate不是列 - 他们是参数用户
提供的格式为mm / dd / yyyy。
我的整个帖子都在解释我会*喜欢*使用BETWEEN
AND语句 - 转换不是我的选择，但是当用户指定与
$ b相同的日期时，这是唯一的
方式。 $ b begindate和enddate。
再一次 - 有人可以解释为什么这个查询没有返回任何记录
当指定begindate和enddate时作为5/3/2006并且
明确交易数据与交易时间？
我的假设是交易是一个datetime数据类型，
包含一个时间，而开始和结束日期只是日期。
比较将在日期时间内完成，其中包括日期和
时间。
交易中BETWEEN请求和结束日期
考虑当我们替换实际价值时的情况。
WHERE
''2006-05 -03 17：12：51.420''BETWEEN
''2006-05-03 00：00：00.000''和
''2006-05-03 00： 00：00.000''
作为交易的一部分的时间是将其置于
范围之外。 BETWEEN;在这种情况下，BETWEEN当然没有
范围，是两个相同的时间点。要匹配它需要24小时b
小时范围，但这不是它的含义。
要使用BETWEEN，你需要将交易减少到一个简单的日期
没有时间参加测试。
Roy Harvey
Beacon Falls，CT
2006年5月3日13:40:16 -0700，Jim Armstrong
< ar ********* @ hotmail.com>写道：
好的 - 我的帖子提到我最初使用
WHERE交易BETWEEN begindate AND enddate
Begindate和enddate不是列 - 它们是参数用户提供的格式为mm / dd / yyyy。
我的整个帖子都在解释我会*喜欢*使用BETWEEN
AND语句 - 转换不是我的选择，但是当用户指定与
begindate和enddate相同的日期时，这是该查询的唯一方式。
所以再一次 - 有人可以解释为什么这个查询返回没有记录
当begindate和enddate被指定为5/3/2006并且有明确的贸易数据与交易时间交换？
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
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.
OK - my post mentioned I originally used
WHERE tradedate BETWEEN begindate AND enddate
Begindate and enddate are not columns - they are parameters the user
supplies in the format mm/dd/yyyy.
My whole post was explaining that I would *prefer* to use the BETWEEN
AND statement - the converting was not my choice, but it is the only
way this query works when the user specifices the same date as
begindate and enddate.
So once again - can someone explain why this query returns no records
when begindate and enddate are specified as 5/3/2006 and there is
clearly trade data with that tradetime?
My assumption is that the tradedate is a datetime datatype that
includes a time, while the begin and end dates are just dates. The
comparison is will be done on datetimes, which include both date and
time.
WHERE tradedate BETWEEN begindate AND enddate
Consider what this can look like when we substitute actual values.
WHERE
''2006-05-03 17:12:51.420'' BETWEEN
''2006-05-03 00:00:00.000'' AND
''2006-05-03 00:00:00.000''
The time that is part of tradedate is putting it outside of the
"range" of the BETWEEN; in this case of course the BETWEEN has no
range, being two identical points in time. To match it needs a 24
hour range, but that is not what it has.
To use BETWEEN you would need toto reduce tradedate to a simple date
with no time for the test.
Roy Harvey
Beacon Falls, CT
On 3 May 2006 13:40:16 -0700, "Jim Armstrong"
<ar*********@hotmail.com> wrote:
OK - my post mentioned I originally used
WHERE tradedate BETWEEN begindate AND enddate
Begindate and enddate are not columns - they are parameters the user
supplies in the format mm/dd/yyyy.
My whole post was explaining that I would *prefer* to use the BETWEEN
AND statement - the converting was not my choice, but it is the only
way this query works when the user specifices the same date as
begindate and enddate.
So once again - can someone explain why this query returns no records
when begindate and enddate are specified as 5/3/2006 and there is
clearly trade data with that tradetime?
这篇关于使用BETWEEN作为日期范围的问题...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
