Problem with using BETWEEN for date range...

[Jim Armstrong]

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

[--CELKO--]

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.

[Jim Armstrong]

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?

[Roy Harvey]

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?

[Erland Sommarskog]

Jim Armstrong (ar*********@hotmail.com) writes:

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))
But this does not make any sense. When I run

select convert(varchar(10), getdate(), 101)

I get

05/03/2006

So if the user enters 2006-05-03, you will also give him hits from
2005???

If you feel compelled to use string format, use format 112 which is
YYYYMMDD, which is possible to compare, and which also is unambiguosly
convertible back to date.

But it would make more sense to simple say:

O.TradeDate BETWEEN @begindate AND @enddate

Provided that is, that TradeDate always have a timeportion of midnight.
And the same provision applies to @begindate and @endate, but I got
the impression that the user enters date only.

If TradeDate also includes hours and seconds, you need to write:

O.TradeDate >= @begindate AND
O.TradeDate < dateadd(DAY, 1, @enddate)

There is one more important thing to observer here: you should avoid
putting TradeDate in an expression. This is because if there is an
index on TradeDate, that index cannot be used if you put TradeDate in
an expression, and performance will suffer.
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)))))
The same applies here, although I'm not really clear what is in TradeTime.
Is TradeDate 2006-05-03 00:00:00 and TradeTime is 2006-05-03 12:23:23?

In such case, I'm not sure why you need to check both.
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))))

And then you have it once more?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[--CELKO--]

>> So once again - can someone explain why this query returns no records [sic] when begindate and enddate are specified as 5/3/2006 and there is clearly trade data with that tradetime? <<

Once again, because there is a DATETIME column which has a time on it.
If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
a single point in time. What you wanted to use was

WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
23:59:59.99'

You might also want to learn ISO standards and use 'yyyy--mm-dd' for
date values.

And do not forget to add constraints to temporal columns in your base
tables.

[Tony Rogerson]

Yes but hang on a minute, what if you don't want a time, holding the time
component is misleading that indicates the data value may contain a time
between 00:00:00 and 23:59:59, being 00:00:00 means the trade was done at
midnight which may well not be true, consider settment date where the trade
needs to settle on a particular day, not by midnight but sometime on that
day.

In SQL Server because we don't have a DATE data type we need to store dates
as an integer in the form yyyymmdd which is unfortunate but the reality.

You might also want to learn ISO standards and use 'yyyy--mm-dd' for
date values.

Rich coming from the guy who doesn't use the standard format himself.

2006-05-03 00:00:00.000 should be written 2006-05-03T00:00:00.000

OR

20060503 if you are only specifying the date.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

So once again - can someone explain why this query returns no records
[sic] when begindate and enddate are specified as 5/3/2006 and there is
clearly trade data with that tradetime? <<

Once again, because there is a DATETIME column which has a time on it.
If you do not give the time, it defautls to 00:00:00.000 Hrs. This is
a single point in time. What you wanted to use was

WHERE tradedate BETWEEN '2006-05-03 00:00:00.000' AND '2006-05-03
23:59:59.99'

You might also want to learn ISO standards and use 'yyyy--mm-dd' for
date values.

And do not forget to add constraints to temporal columns in your base
tables.

[Erland Sommarskog]

Tony Rogerson (to**********@sqlserverfaq.com) writes:

Yes but hang on a minute, what if you don't want a time, holding the
time component is misleading that indicates the data value may contain a
time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
done at midnight which may well not be true, consider settment date
where the trade needs to settle on a particular day, not by midnight but
sometime on that day.

In SQL Server because we don't have a DATE data type we need to store
dates as an integer in the form yyyymmdd which is unfortunate but the
reality.

Only if you like to throw out babies and bathtubs simultaneously.

All our settledate columns are datetime, or more precisely aba_date,
which is a datetime with a rule bound to that screams blue murder if
you try to use anything else than 00:00:00.000 for the time porttion.

And, no, our customers do not settle at midnight.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

> All our settledate columns are datetime, or more precisely aba_date,

which is a datetime with a rule bound to that screams blue murder if
you try to use anything else than 00:00:00.000 for the time porttion.

And, no, our customers do not settle at midnight.
Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?

To quote celko, using datetime to hold a pure date is really a kludge ;),
seriously though, even though you have a check constraint to prevent times
over than midnight, it shouldn't have a time at all and any query result
should not contain a time component, it ensinuates that settment date is not
a date but actually a date with a time, so to the application developer or
third party vendor accessing your webservice etc... it might well appear
that trades should be settled by midnight.

All the trading systems I've worked with ALL use the integer data type to
hold pure dates.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... Tony Rogerson (to**********@sqlserverfaq.com) writes:

Yes but hang on a minute, what if you don't want a time, holding the
time component is misleading that indicates the data value may contain a
time between 00:00:00 and 23:59:59, being 00:00:00 means the trade was
done at midnight which may well not be true, consider settment date
where the trade needs to settle on a particular day, not by midnight but
sometime on that day.

In SQL Server because we don't have a DATE data type we need to store
dates as an integer in the form yyyymmdd which is unfortunate but the
reality.

Only if you like to throw out babies and bathtubs simultaneously.

All our settledate columns are datetime, or more precisely aba_date,
which is a datetime with a rule bound to that screams blue murder if
you try to use anything else than 00:00:00.000 for the time porttion.

And, no, our customers do not settle at midnight.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Tony Rogerson (to**********@sqlserverfaq.com) writes:

Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?
Computed column? Hey, this system started its life in SQL Server 4.x. And
you cannot accuse for being too quick to leave SQL 6.5. I think we have
two computed columns in the system.

Yes, the time portion is a bit of noice in Query Analyzer. For the client,
it is a non-issue.
All the trading systems I've worked with ALL use the integer data type to
hold pure dates.

One of our competitors seems to go by char(8). (They're not using SQL
Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
over several of their customers and converted their data to our system.
I've seen some completely hilarous dates there.

Using integer or char(8) for dates is in my opinion an even worse solution,
as you run the risk of getting dates like 20060230 and that in the system.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Tony Rogerson]

> Using integer or char(8) for dates is in my opinion an even worse

solution,
as you run the risk of getting dates like 20060230 and that in the system.
Well not really, its the same argument i'd have with a time component of the
datetime, both are fixed with a check constraint.

I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount to
a lot more IO for larger trading systems.....

check( ISDATE( 20060230 ) = 1 ) which is simpler then the check constraint
needed for slicing off / checking for the time component!

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... Tony Rogerson (to**********@sqlserverfaq.com) writes:

Presumeably you also have a computed column that strips away the time
component so only the date gets passed back to the client?

Computed column? Hey, this system started its life in SQL Server 4.x. And
you cannot accuse for being too quick to leave SQL 6.5. I think we have
two computed columns in the system.

Yes, the time portion is a bit of noice in Query Analyzer. For the client,
it is a non-issue.

All the trading systems I've worked with ALL use the integer data type to
hold pure dates.

One of our competitors seems to go by char(8). (They're not using SQL
Server. Their system runs on AS 400, so I guess it's DB2.) We have taken
over several of their customers and converted their data to our system.
I've seen some completely hilarous dates there.

Using integer or char(8) for dates is in my opinion an even worse
solution,
as you run the risk of getting dates like 20060230 and that in the system.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Erland Sommarskog]

Tony Rogerson (to**********@sqlserverfaq.com) writes:

I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
to a lot more IO for larger trading systems.....
So that is an argument that carries some weight.

But integer data for dates is so much messier to deal with, not the
least for the client.
check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
constraint needed for slicing off / checking for the time component!

Nah, I would write it as

CREATE RULE aba_date_rule (isdate(ltrim(str(@x))) = 1

I hate to rely on implicit conversions from integer to string.

(And it is a rule, as I only want it one place.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Alexander Kuznetsov]

Erland,

There is another approach, you can number days sequentially, such as
datediff(day, '20000101', your_original_date). In many cases it allows
to use a 2-byte integer, and that may be quite a difference. I use
dateadd to get dates from those integers, but but dates are needed for
display purposes only, not frequently. I also use a calendar table with
the the same integer as PK.

[Tony Rogerson]

Make sure @x is nvarchar then, because thats what ISDATE takes.

But integer data for dates is so much messier to deal with, not the
least for the client.
I totally agree, but its more messy have the time component on queries in my
experience.

Having said that i always convert the integer to a smalldatetime when
creating a fact table purely because its easier for the time dimension on my
dimensional model.....

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Erland Sommarskog" <es****@sommarskog.se> wrote in message
news:Xn**********************@127.0.0.1... Tony Rogerson (to**********@sqlserverfaq.com) writes:

I've not mentioned the space issue - 4 bytes ve 8 bytes which can amount
to a lot more IO for larger trading systems.....

So that is an argument that carries some weight.

But integer data for dates is so much messier to deal with, not the
least for the client.

check( ISDATE( 20060230 ) = 1 ) which is simpler then the check
constraint needed for slicing off / checking for the time component!

Nah, I would write it as

CREATE RULE aba_date_rule (isdate(ltrim(str(@x))) = 1

I hate to rely on implicit conversions from integer to string.

(And it is a rule, as I only want it one place.)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx